Need a Formula to Countif a Range based on an input

0
votes

enter image description here

Above is a snippet of data that I have (in Sheet1). My task is to count the number of times "C" is in, let's say from B to C, (the formula will be written in Sheet2 for this). I have the code for that and it works fine.

But what can I do to get the count based on a particular UserID entered in cell A1 of Sheet2. For ex: if 'Sheet2'!A1=3 then the count in row 3 of form B to C should be given:

(Result = Countif('Sheet1'!"B2:B4","C"))

Hope my explanation was understandable. I need a formula for this not macro.

I tried to get the row ID using Index Match but I am not able to combine it with CountIf.

Both VLOOKUP and INDEX MATCH can be used only if I want one cell value, but not multiple values in same row.

Result = Countif('Sheet1'!"B2:B4","C")
2
excelexcel-formula
Check COUNTIFS function. It allows you to use more conditions before making the count. It's exactly what you need. - Foxfire And Burns And Burns

2 Answers

0
votes

Not the most elegant formula, but the following should achieve your desired result:

=COUNTIFS(Sheet1!A:A,Sheet2!A1,Sheet1!B:B,"C")+COUNTIFS(Sheet1!A:A,Sheet2!A1,Sheet1!C:C,"C")+COUNTIFS(Sheet1!A:A,Sheet2!A1,Sheet1!D:D,"C")+COUNTIFS(Sheet1!A:A,Sheet2!A1,Sheet1!E:E,"C")+COUNTIFS(Sheet1!A:A,Sheet2!A1,Sheet1!F:F,"C")
0
votes

If you know how to use Names in excel, you can define the following names:

  • Rng_Data represents all your data in Column B to F
  • List_User represents all your data in Column A

then use the following formula to find the desired count:

=SUMPRODUCT((Rng_Data="C")*(List_User=Sheet2!A1))

Where Sheet2!A1 is the given User ID in cell A1 on your second sheet. You can drag this formula down to apply to the rest.

In my following screen-shot, I used two sets of data to test the outcome. The first set is the same as yours and the User ID in Column A is unique. In the second set of data, there are duplicated User ID and the formula still finds the occurrence of C for the given User ID as highlighted.

SUMPRODUCT sometimes work like COUNTIFS but in a more powerful way.

You can read this article to learn more about SUMPRODUCT: Excel SUMPRODUCT function with formula examples

Cheers :)

Solution