• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIFS - skip a range- and-criteria set if condition met

C

cacos

Member
Hi everyone!

This is probably an easy one but I just can't figure it out.

In a countifs formula, I want it to skip a certain set of range and criteria- let's say the 2nd and 3rd- if a certain condition is met.

For example, something like:

countifs(range1,criteria1,if(A1="a","",range2,criteria2), etc....

Of course that doesn't work, but to illustrate that it would be something like "if cell A1 is equal to "a", skip the whole 2nd set and move to range3 and criteria3".

I hope it makes sense.

Thanks!!
 
Cacos

Can you post a workbook with an example of what the answers should be under certain criteria?
 
Hi Lucas ,

See this file.

The formulae are in column H. Both the SUMPRODUCT version and the COUNTIFS version are given.

Narayan
 

Attachments

  • Book1.xlsx
    8.4 KB · Views: 19
Thanks Hui and Narayank, actually I think it's something different.

I'm uploading a sample file. You'll see there's a column for Car model, one for the State and a third one for the Income.

And on cell E3 there's a Yes/No field that I'd want it to condition wether the COUNTIFS will consider the 3rd criteria, the one with the Income.

If on E3 the value is "No", I want the same formula to count the first 2 sets of range/criteria but ignore the one for the Income. If it's "Yes", it should not ignore it.

Please consider that I have a lot more criterias in my actual file so it makes much more sense, and many of those would be conditional.

Thanks again!
 

Attachments

  • Chandoo.xlsx
    10.9 KB · Views: 23
Hi,

Why don't you use two countifs formula within an IF formula like belwo:

=IF(E3="Yes",COUNTIFS($A$4:$A$20,E6,$B$4:$B$20,F6,$C$4:$C$20,">"&G6),COUNTIFS($A$4:$A$20,E6,$B$4:$B$20,F6))

Regards,
 
Yes, that works, but in my actual file there will be like 10, 12 conditional criteria sets, and taking this approach would end up in a huge formula.

I was just wondering if there's a simple approach, which I've found it to always be the case in Excel ;)
 
Hello Cacos,

Try this Array Formula with CTRL+SHIFT+ENTER (will INCLUDE blank also, if condition is set to ignore)

=SUM((A4:A20=E6)*(B4:B20=F6)*IF(E3="Yes",C4:C20>G6,1))

Add more IF with same layout. *IF(Condition,Range Criteria,1)
 
That's great Haseeb let me try that. It shouldn't be slower in calculating than countifs right? Because there's over 20K rows of data.

Thanks
 
Hi forum

Can anyone think of a non-array approach to this formula, considering there are a lot of criteria and the ranges are thousands of rows long.

Thank you!
 
You must log in or register to reply here.
Back
Top