Complex CountIfs with Sumproduct...

[Scotster]

I've never used SumProduct before so I've cobbled this together after a bit of googling. The Sumproduct works great and reports what I would expect but I need to eek out more to get what I need.

Here is the formula that I wanted to use.....

=COUNTIFS(O:O,0,Q:Q,"No",R:R,"<" & N:N)

Unfortunately the last part of the formula doesn't work the way I wanted. I found this out after breaking the formula down into portions and checking the results...

=COUNTIFS(O:O,0)
=COUNTIFS(Q:Q,"No")
=SUMPRODUCT(--(R:R))
<n:n))

I'm left with....

=COUNTIFS(O:O,0,Q:Q,"No")
=SUMPRODUCT(--(R:R))<n:n))

How do I combine the above for the multiple criteria?

Many thanks

PS Does anyone else find it incredibly frustrating typing on here? I need to re-press every 2nd or 3rd key due to them being dropped.

Example: just tried o tpe this without stppingand ended upwth an awful lot of the ketroks missing from wha I was trying to rite. It incudes spces and all srts.</n:n))
</n:n))

 

[steve the fish]

Re: Help with Complex CountIfs with Sumproduct...

Like this?

=SUMPRODUCT(--(O:O=0),--(Q:Q="No"),--(R:R < N:N))<n:n))


but you really shouldnt use the full column references so if possible make it like the following as an example:

=SUMPRODUCT(--(O1:O100=0),--(Q1:Q100="No"),--(R1:R100 < N1:N100))<n1:n100))< html=""></n1:n100))<></n:n))

 

[FormR]

<n:n))
<n:n))
Example: just tried o tpe this without stppingand ended upwth an awful lot of the ketroks missing from wha I was trying to rite. It incudes spces and all srts.</n:n))

Hi, I've not read it all - but there is a thread here discussing the issue and possible solutions.

https://www.mrexcel.com/forum/about...g-text-into-reply-window-excel-questions.html</n:n))

 

[Scotster]

Re: Help with Complex CountIfs with Sumproduct...

Like this?

=SUMPRODUCT(--(O:O=0),--(Q:Q="No"),--(R:R < N:N))<n:n))


but you really shouldnt use the full column references so if possible make it like the following as an example:

=SUMPRODUCT(--(O1:O100=0),--(Q1:Q100="No"),--(R1:R100 < N1:N100))<n1:n100))< html=""></n1:n100))<></n:n))

Perfect, works a treat!

I will likely change to proper references but at the moment it varies daily. I'll be adding the formula via VBA eventually so I can find the last row.

Thanks again!