Excel Magic Trick #121: Count With More Than 1 Criteria Condition

[tekesbur]

great website! i was wondering why didn't you use ctrl+shift+enter in the sumproduct option? thanks

[ExcelIsFun]

SUMPRODUCT can handle arrays without using Ctrl + Shift. SUMPRODUCT, LOOKUP, and a few other functions can handle arrys without Ctrl + Shift + Enter. They are just programmed that way.

[tekesbur]

@ExcelIsFun Awesome thank you!!!

[ExcelIsFun]

You are welcome!

[Picture111]

Hi, Thanks for posting this video. when you highlight the range would it be possible to make the criteria = to a cell within in the range unlocked?

something like this =Sum(IF($B$11:$B$22=B11)and the same for the other range? would it work?

[ExcelIsFun]

Yes, becasue we were not copying the formula in this example.

[Ibarra2020]

Sould this work if i have filters on?

[ExcelIsFun]

The formulas will work, but it will look at all the data instead of the filtered data. If you want functions to look at the filtered data you can use the Totals row in an Excel Table (Ctrl + T) or Excel List (Ctrl + L). The Totals row has a drop-down where you can select the function. The Totals row uses the SUBTOTAL function.

[Ibarra2020]

@Ibarra2020 I was working on this for a while. It works grate. Thank you. Your the best.

[sportsaddict84]

hi there,

thank you so much with this video and others. it definitely helps me a lot with my work. thanks a million!

[ExcelIsFun]

Dear sportsaddict84,

You are welcome!

--excelisfun

[Dave51262]

MIke - Thanks for this! This is something I can use at work immediately that will save me a lot of time. Great!

[ExcelIsFun]

Dear Dave51262,

I am glad it helps!

Here is another option for the above table:

=SUMPRODUCT(--($B$11:$B$22=$B2­5),--($C$11:$C$22=C$24))

If you have a large spreadsheet, the double-negative version is the fastest

--excelisfun