Let’s say I have 2 columns. Column A has fruits (apple or banana) and Column B has dates (format: mm/yy/yyyy) representing the date (365 dates Jan to Dec) on which I ate the afromentioned fruit listed in coulmn A
I would like to use multiple criteria to find the number of times I ate “Apple” in the month of "August (08)"
How would I frame my COUNTIFS? Is there any other way that works better for multiple criteria summarizing other than Pivots.
Was going to say this. I guess you could nest the statement… countif Month is August, countif it’s Apple… and leave two blank cells so you can type in Months in One and fruit in the other. Reference the cells for the two Ifs
To be honest I would just use a pivot though
I need something like this
COUNTIFS(A:A,“Apple”,MONTH(B1:B365),8)
You can use COUNTIFS(), but you will need to add a third column with MONTH({dates column}).
The formula is just =COUNTIFS({fruits column}, “apple”, {months column}, 8) .
It’s a predefined read-only template, so can’t add/remove any columns.
Is there around way using SUMPRODUCT? Nested IFs?
countifs(Column A,“Apple”, Column B, “>8/1/2008”,Column B,"<8/31/2008")
Might have to use an array function. Note that in this case you need to use the number for the month (ie. August, use the number 8 as your criteria). Remember, Ctrl+Shift+Enter to submit the formula as an array, and don’t type in the curly brackets, Excel does that part itself.
{=SUM(IF(A1:A8=B10,IF(MONTH(B1:B8)=A11,1,0),0))}
Where A1:A8 is your list of fruits, B10 is your fruit criteria (Apple), B1:B8 is your dates, and A11 is the number for the month criteria.
Basically we can nest the if’s, and use ranges instead of single cell references, and then the array and sum combination tell it what to do with each result of the nested IF’s. You can’t use AND/OR on array’s so this is a common workaround.
It takes more space, but the way I’d probably handle this is have a column that tests ( IF(A=‘APPLE’,1,0) ) then a column for testing month ( IF(month(B)=8,1,0) ).
I’d then sum them up using SUMPRODUCT on the two columns.
To make it more flexible, I’d put 'APPLE" at the top of the IF column testing for apple and use a reference $, so I could change it to “BANANA” or something else if I wanted to change the test and the sum. Same with the date.
If you have really realy long columns, however, this could make the spreadsheet drag when you update it, but for moderate sized stuff, it’s easier than trying to string together the countif format.
The set notation stuff works ok for some of these things, but I find it so cumbersome I try to avoid it as much as I can.
Thanks guys, SUMPRODUCT did the trick
=SUMPRODUCT((A:A=“Apple”)*(B:B>=DATE(2013,8,1))*(B:B<=DATE(2013,8,31)))
Damn sumproducts, I still haven’t figured out there more advanced uses. From what I can tell that formula returns a “1” every time column A is apple, times a “1” every time date is greater than/equal to August 1st? Not sure what that last (B:B is for then, or if you cut part off.
Several wasy to skin a cat but this is what the “And” function is for…
Something like this should work…
=(IF(And(A:A=“Apple”, B:B=“August”),1,0))
Then Sum all the 1’s
I’ve always had tons of the trouble with countif and countifs working… its been easier to get the results I want in other ways than figuring out what actually went wrong.
Do you know about the “Evaluate Formula” function in Excel?
^It’s really more of a tool than a function.
COUNTIFS(A:A, “Apple”, B:B,">="&StartDateCell, B:B,"^="&EndDateCell)
Where StartDateCell and EndDateCell in this case would be 8/1/2008 and 8/31/2008, respectively. You can just change those cells to evaluate for different months. The ^ should be the less than sign but it does not let me post that…weird.
^ I tried to post the same formula as you did but my post was cut right after "