Sumifs and Countifs limitations with ors and AND: Excel formulas:

[pedie]

Hi, below is the current excel formula.
Is there any limitations with Sumifs and Countifs with or combinations
'=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27
,{"NY","GA","LA"}))

for exampel if I want it this way {"NY","GA","LA","AZ","TX"})), will it still give me accurate answer?


Thanks in advance.

 

[Matty]

Hi,

Could you explain what you're wanting the formula to do? A small example would help.

One thing I did notice is the ";" in {"FEC";"ORANGE"}. Is there a reason you have used this?

Matty

 

[pedie]

Yes, Ron adviced that we use ; yesterday. I am trying to add many or condition to one col. to return the result I want if any critiria is met...

Thanks again.

 

[Matty]

I could be wrong, but my understanding is that vertical arrays should be separated by a ";", whereas horizontal arrays should be separated by a ",".

What is the SUM element in the formula you posted supposed to do?

Matty

 

[pedie]

Matty, thanks again for getting back...
What i want is to count if column b = "May",
Column C = any of either "FEC" or "ORANGE"
Column A any either "NY" or "GA" or "LA" or "AZ" or "TX"...

Thanks again Pedie.

 

[Matty]

Try:

=SUM(IF(B2:B27="May",IF(ISNUMBER(MATCH(C2:C27,{"FEC","ORANGE"},0)),IF(ISNUMBER(MATCH(A2:A27,{"NY","GA","LA","AZ","TX"},0)),1))))

Array entered...

Matty

 

[Haseeb Avarakkan]

Try,

Enter the all value, NY,GA,LA... n a range, say in E2:E6, then

=SUMPRODUCT(COUNTIFS(A2:A27,E2:E6,B2:B27,"May",C2:C27,{"Fec","Orange"}))

 

[Aladin Akyurek]

Testing which involves two sets of criteria requires a V,H or H,V organization... V stands for vertical and H for horizontal.

V,H as in:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27,{"NY","GA","LA","AZ","TX"}))

yields an intermeadiate result like:

SUM({1,0,1,0,1;0,1,0,0,0})

H,V as in:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

yields an intermeadiate result like:

=SUM({1,0;0,1;1,0;0,0;1,0})

whereas...

V,V and H,H as in:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY","GA","LA","AZ","TX"}))

yield the following intermediate results, respectively:

=SUM({1;1;0;0;0})

=SUM({1,1,0,0,0})

Using a cell range instead of an array constant does not show a different behavior.

Concluding...

I guess H,V or V,H are more often than not the intended behavior.

 

[pedie]

Both worked perfect...thanks alot!
Thanks for helping again!

 

[Matty]

Hi Aladin,

Thanks for sharing your thoughts.

With array constants such as those pedie is working with, given that they are being applied to Columns of data, I suppose it doesn't matter whether the "," or the ";" delimiter is used, as long as the approach is consistent. Or am I missing something here?

Matty