David has attended:
Excel Advanced course
Excel Advanced course
SUMIF & IF Formula Query
Hi Anthony,
Is there a formula you can use that would be able to do the following:
IF Cell A1 = "January" and IF Cell B1 = "Morning", display "x"
(i.e. 2 IF Formula's in one statement?) I am guessing I would need to use the AND function? Is this correct? However I would also like to sum the totals in all other cells in a particular table that contain "January" and "Morning."
I can send you the spreadsheet that I am working on if that makes it any easier understand? I understand you can use a PIVOT table to calculate the answer, however I just wondered if there was a formula that you can use instead?
Many Thanks,
David
RE: SUMIF & IF Formula Query
Hi David, thanks for your query and apologies for the delay. Your first formula needs to look like this:
=IF(AND(A1="January", B1="Morning"), "X", "They're not what I want")
Your second formula is slightly more complicated. Create three columns, the first with Monday through to Friday, the second with January through to December and the third with 1 through to 12 and autofill down so you get some repeats. Then in an empty cell put this formula:
=SUMPRODUCT(--(A1:A24="Monday"),--(B1:B24="January"),C1:C24)
...and yes, you do need the "--"! You can have as many criteria in there as you need; what you are basically doing is summing the product of an array. Have a play with it and see how you get along.
Hope this helps,
Anthony
