Excel Magic Trick 1513: COUNTIFS from Multiple Cells!?!? Array Formula or Logical Formula?

In this video see how to count values that are greater than a hurdle when the values in in noncontiguous cells (cells not next to each other). See an Array Formula that uses SUMPRODUCT and CHOOSE and a Logical Formula.

[riteshpandey]

You are the best Youtuber on Excel. No one better then you in excel. because there is so much material to learn and become excel master on your channel.

[nicor]

Hi Mike, your videos, the way you talk and your sense of pedagogy are just awesome !
Thanks a million for sharing these pieces of art.

[FabioGambaro]

The more I see SUMPRODUCT in action, the more I love it! Great as usual :-)

[mazarata]

The power of your tutorials lies in the cristal clear reasoning. We always get an answer on the "why". Thanks for that.

[LeilaGharani]

I love the SUMPRODUCT version! Many thanks Mike :)

[MsNoeynono]

The best you tube channel ever! This channel has helped me so much, thank you!

[Howtoexcelatexcel]

Just love the SUMPRODUCT solution, thumbs up Mike

[BillSzysz]

Thanks Mike :-))
Additional solution: =INDEX(FREQUENCY((C1, C4, C5, C12, B4, E5), 72), 2)
:-)))

[barttitulaerexcelbart]

Nice to see an example of CHOOSE like this. I tried to make a named range, that works. And I could use this name for SUM, but for SUMIF I got an error "VALUE". nice try....Next time better...One tip that does work: After you copied the parts (C12>E12) a few times, I use following "trick": Just "drag" the border of cel C12 to the desired place and just repeat this. This works fast as well. Greetings, Bart

[wayneedmondson]

Hi Mike.. clever tricks. I learn something new from you every day. Thanks and Thumbs up!

[JeremyMcMahan]

Great trick with the CHOOSE function! Thanks!

[davebowman]

CHOOSE can be very handy, thanks Mike

[masterof]

Wow, really magical! I don’t remember I have ever used CHOOSE() before... thank you!

[simfinso]

Excel is treat for me whenever I watch video.Thnaks sir.& plz make a video on "Mmult " function again.

[MalinaC]

Hehe, SUMPRODUCT saved the day, as usually :)

[horatiolui]

hey thanks for walking us through the use of CHOOSE and SUMPRODUCT. Lets not forget the double negative trick on an array operation too.

Would COUNTIF do the same job? like F12=COUNTIF(A1:E5, ">"&E12). Surely if you want C12 to be included can we add another COUNTIF so that the entire formula will be like F12=COUNTIF(A1:E5, ">"&E12)+COUNTIF(A6:D12, ">"&E12)?

[esteban]

HI Mike love your videos, i need some help, I have four colums with data in which the first colum has dates and the order 3 have text data, i am trying to count the number of occurrences on the second colum between a date range.

[ismailismaili]

brilliant I like the idea thank you so much for sharing Mr. Mike

[thriftymillionaire]

i need help comparing two cells with a high and low value, one of the cells is blue. Return 1 if the lowest value is blue color or return zero if the higher value is blue color. how do i perform this task in excel?

[petermyran]

I heartily agree with the previous comment by ritesh!! I thought AGGREGATE((2, 6, (--(CHOOSE({1, 2, 3, 4, 5, 6}, C12, C1, B4, C4, C5, E5)>E12))) would have worked but I get a #VALUE error. I don't understand why.