COUNTIFS problem
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?
Last edited by javajoemorgan; 10-15-2009 at 11:32 AM.
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))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
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"????
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.
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))
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..
Re: COUNTIFS problem
=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??
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))
Re: COUNTIFS problem
Holy crap!
It works... and that's all I care about!!!!
Thanks a million!!!
Last edited by javajoemorgan; 10-15-2009 at 11:30 AM. Reason: Solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks