Combining Countifs with 3 or more criteria

[Rameses]

Hi there

I wish to combine the following COUNTIFS together but am becoming stuck and stuckier.. as I cant seem to get past the first 2 COUNTIFS

Essentially (C2:C47,"Y") is the most important

Column C 1st must be a Y to continue with COUNTIFS which are meant to be adding the number of times "N" or "-1" or "-2" or "-3" or "-4" appear in column W

The formula I have attempted is as below

(C2:C47,"Y",W2:W47,"N",0,W2:W47,"-4>0")

any comments will be much appreciated

many thanks

Rameses

 

[jasonb75]

I don't think countifs can work with an AND / OR scenario, you would need a formula for each possible result in W, not 1 to cover all.

The following array formula does what you're asking,
=SUM((C2:C47="Y")*(W2:W47={"N",-1,-2,-3,-4}))

copy / paste the formula then enter it with shift + ctrl + enter.

 

[Rameses]

Re: `Editing an Array Formula

Thank you so much

The last formula worked really well

However I forgot to add soem extra conditions to column C

I am looking for the array to be based on if any of the following conditions in column C a "Y", "P", "S", "A" are met

many thaks once again
all comments are appreciated

Rameses

 

[jasonb75]

Re: `Editing an Array Formula

I'm sure one of the MVP's will have a solution to this but I don't think it can be done as a single formula, I've tried various ways of entering multiple arrays in a single cell, all of which produce either an error or an incorrect result.

The only solution I have so far is to use a helper column

Enter the formula =OR(C2={"Y","P","S","A"})*OR(W2={"N",-1,-2,-3,-4}) into say Z2 and copy it down to Z47 (this column could be hidden after)
then =SUM(Z2:Z47) would give the correct result for your criteria.

Both are regular fomulae and do not need to be entered with shift / ctrl / enter.

Hope this helps.