Countif in arrays with two conditions

Hello everyone

In this link I have put the same issue
http://www.eileenslounge.com/viewtopic.php?f=30&t=25267


But I don't need to rely on cells (colored cells are not required)

First have a look at this code that count the range("B8:AP8") on a criteria 4,3,2,1,0 ..Run the demo code to see the results in range("AR7:AV8")
I need to extend the criteria ..
To count if :
---------------
range("B8:AP8") on a criteria 4,3,2,1,0
range("B7:AP7") has one of the values in range("B1:F1")

The expected results would be like that snapshot

Untitled.png

for example : the number 3 happened 13 times in range("B8:AP8")
And in range("X7:L7") the numbers 16 & 18 & 26 & 28 occurs in this range
So the result for 3 is 4
Hope it is clear

The standard way to do it is to create multiple COUNTIFS and add them together eg

Please Login or Register  to view this content.

However, I believe that some form of SUMPRODUCT can do it a bit neater.....Im not clever enough on a Monday morning to do it though

Hi,

You might use a formula such as this
=SUMPRODUCT(COUNTIFS($B$8:$AP$8,AR7,$B$7:$AP$7,$B$1:$F$1))
unless you specifically require a UDF.

Originally Posted by xlnitwit

Hi,

You might use a formula such as this
=SUMPRODUCT(COUNTIFS($B$8:$AP$8,AR7,$B$7:$AP$7,$B$1:$F$1))
unless you specifically require a UDF.

Cant believe I was so close, for some reason i was sure you needed {} around the range $B$1:$F$1 but couldnt get it to work, didnt even bother trying it without!

You only use the {} notation with array constants- such as {1,2,3}- not with ranges.

Thanks a lot for reply
I prefer using UDF if possible. If you looked at the other thread you will find some clues but not the final yet
Thank you very much for sharing

Originally Posted by YasserKhalil

I prefer using UDF if possible.

It will be less efficient to do so- is that not a concern?

In fact, it is a part of huge project I am working on and no place for formulas .. I previously relied on formulas and it was a disaster when the data get bigger. I will need to do the task many many times so I think udf will be very helpful
I will not use it in sheet directly. It will be part of other codes

It is solved on this link
http://www.eileenslounge.com/viewtop...195948#p195948