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

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

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.

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

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.

Try:

Array entered...

Matty

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"}))

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.

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

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