COUNTIFS problem

[javajoemorgan]

This is my countifs function:

=COUNTIFS(Oct2009!$AV$2:$BG$3009,$A2,Oct2009!$AV$2:$BG$3009,E$1)

Either one of these works. That is:

=COUNTIFS(Oct2009!$AV$2:$BG$3009)

produces 110 and

=COUNTIFS(Oct2009!$AV$2:$BG$3009,E$1)

produces 2295

I was thinking those combined criteria would count the matches in the 2nd formula agaist those of the first, or at least, that's the way I interpret the docs.

Am I clear enough?

[NBVC]

The Countifs arguments work like ANDs... ie all the conditions must be true...

you are wantiing the OR conditions... either condition is True... so you will need to add the 2 Countif functions (n.b. you only need Countif() for one criteria.

=COUNTIF(Oct2009!$AV$2:$BG$3009,$A2)+COUNTIF(Oct2009!$AV$2:$BG$3009,E$1)

or use Sumproduct()

=Sumproduct((Oct2009!$AV$2:$BG$3009=$A2)+(Oct2009!$AV$2:$BG$3009,E$1))

[javajoemorgan]

I didn't explain my goal correctly.

One count is 110 where a range matches a value in a column, that is, the word "Pie" shows up 110 times in my whole data set.

Now, from that, I need to know how many of those are "Apple" pies. There are 2295 "Apple"s, but only X number of them are associated with "Pie"

Is that better? So, if:

=COUNTIFS(Oct2009!$AV$2:$BG$3009,$A2)

Is the number of "Pie"s, and

=COUNTIFS(Oct2009!$AV$2:$BG$3009,D$1)

Is the number of "Apple"s, then how many Apple Pies do I have? The Data within Oct2009!$AV$2:$BG$3009 looks something like this:

Pie|Apple|Cherry|BlueBerry|Pecan|Lemon
Turnover|Apple|BlueBerry|Rasberry
Pie|Cerry|Pecan
Ice Cream|Vanilla|Cherry|Lemon|Cookie Dough
Turnover|Lemon|Cherry|BlueBerry|Pecan

In my other spreadsheet, I have ( I hope this displays right):

-----------Apple Cherry BlueBerry Pecan Lemon etc...
Pie
Turnover
IceCream
Candy

What is the formula such that I can get the count of times the word "Apple" is associated with "Pie"????

[NBVC]

Maybe:

=COUNTIFS(Oct2009!$AV$2:$AV$3009,$A2,Oct2009!$AW$2:$BG$3009,E$1)

assuming Pie is always in first column per your sample and the fruits are in the next few columns.

[daddylonglegs]

Hello NBVC, javajoe

I can't test with Excel 2007 right now but I'm assuming that COUNTIFS works like SUMIF, i.e. that there has to be a 1 to 1 relationship between the ranges, in which case that formula would only count fruits in column AW, in which case you'd need SUMPRODUCT, i.e.

=SUMPRODUCT((Oct2009!$AV$2:$AV$3009=$A2)*(Oct2009!$AW$2:$BG$3009=E$1))

[NBVC]

I can't test with 2007 right now either.. that is why I said "maybe"... but you are probably right, daddylonglegs... I did test it in 2003 with Sumproduct() as you show, but I (perhaps wrongly) assumed that the Countifs() version would work...so posted that..

[javajoemorgan]

=SUMPRODUCT((Oct2009!$AV$2:$AV$3009=$A2)*(Oct2009!$AW$2:$BG$3009=E$1))

Is the answer, it appears... but I cannot begin to make common sense of it in my head. I would have thought that would have multiplied 110 by 2295, which is not what I wanted so I never even thought of looking there...

Now, I need one more thing....

Sometimes, rows in the original may show up like this:

Pie| *** Nothing to its right ***
Turnover| *** Nothing to its right ***
Pie| *** Nothing to its right ***

How do I count how many times "Pie" shows up with no flavor??

[daddylonglegs]

Try like this

=SUMPRODUCT((Oct2009!$AV$2:$AV$3009=$A2)*(SUBTOTAL(3,OFFSET(Oct2009!$AW$2:$BG$2,ROW(Oct2009!$AW$2:$BG$3009)-ROW(Oct2009!$AW$2),0))=0))

[javajoemorgan]

Holy crap!

It works... and that's all I care about!!!!

Thanks a million!!!