98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » SUMIF & IF Formula Query
SUMIF & IF Formula Query
Resolved · Low Priority · Version 2003
Wed 4 Aug 2010, 13:28Delegate David said...
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
Tue 10 Aug 2010, 11:23Trainer Anthony said...
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
Tue 17 Aug 2010: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Shortcut keys to move between sheetsInstead of clicking on a sheet tab to view a sheet, use the following keyboard shortcuts to move between sheets in the same file: |
