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 statement with multiple criteria
SUMIF statement with multiple criteria
Resolved · Medium Priority · Version 2007
Mon 7 Jun 2010, 13:54Delegate Tom said...
Tom has attended:
Excel VBA Intro Intermediate course
Access Intermediate course
SUMIF statement with multiple criteria
Hi,
I am having trouble with SUMIF statements with multiple criteria and wondered if someone could help...
Here is my formula...
=SUMIF(('Full data table'!B:B="April"), ('Full data table'!H:H="BR ONE"), ('Full data table'!C:C=(TEXT(TODAY(), "yyyy")-1)), ('Full data table'!O:O))
So...
Where 'Full data table'!B:B="April", 'Full data table'!H:H="BR ONE" and 'Full data table'!C:C=2009 add the entry in column: Full data table'!O:O to any others that pass the multiple criteria.
It just doesnt work, I've tried it with *s instead of commas and alsorts but havent found the answer.
I have been trying to get this right for ages and would really appreciate any help you could give.
Thanks,
Tom
Tue 8 Jun 2010, 16:44Trainer Newson said...
RE: SUMIF statement with multiple criteria
Tom
Would it be possible to have a sample of some data to see what your actually trying to do.
Its just a bit cryptic trying to see what your trying to do.
Thanks
Wed 9 Jun 2010, 09:50Trainer Newson said...
RE: SUMIF statement with multiple criteria
Tom if you could send me a sample of what you are trying to do at the following e-mail address:
newson@stl-training.co.uk
That would be greatly appreciated
Wed 9 Jun 2010, 16:11Trainer Andrew said...
RE: SUMIF statement with multiple criteria
Hi Tom
In Excel 2007 SUMIF() is joined by a new SUMIFS()
Here is some information from Microsoft about this feature. Could this be more useful?
http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx?CTT=5& ;origin=HA010277524
Let me know what you think.
Kind regards,
Andrew
Thu 10 Jun 2010, 15:55Delegate Tom said...
RE: SUMIF statement with multiple criteria
Hi Andrew,
Thanks very much for your reply. I'm so close now, this works if I want a static year (2009)...
=SUMIFS('Full data table'!O:O, 'Full data table'!B:B, "=April", 'Full data table'!H:H, "=BR ONE", 'Full data table'!C:C, "=2009")
But if I want it to be dynamic and only pick out last year by using this formula - ('Full data table'!C:C=(Text(Today(), "yyyy")-1)) - the SUMIFS function doesnt seem to like putting a formula in. I tried various syntax variations including...
=SUMIFS('Full data table'!O:O, 'Full data table'!B:B, "=April", 'Full data table'!H:H, "=BR ONE", 'Full data table'!C:C, "=(Text(Today(), ""yyyy"")-1)")
But I cant get it to accept it and give the right answer.
Can you help at all?
Thanks,
Tom
Thu 15 Jul 2010, 09:24Trainer Andrew said...
RE: SUMIF statement with multiple criteria
Hi Tom
How did you get on with the SUMIFS statement? If you are still having problems and would like me to take a look you can email an example to info@stl-training.co.uk. Mark it for the attention of Andrew and I'll see if I can help.
Kind regards,
Andrew
Mon 27 Sep 2010, 11:01Trainer Andrew said...
RE: SUMIF statement with multiple criteria
Hi Tom
Just wanted to check in with you regarding your query on multiple criteria with sumif. Did you manage to find a solution that produced the results you were looking for?
Kind regards,
Andrew
Mon 27 Sep 2010, 11:08Delegate Tom said...
RE: SUMIF statement with multiple criteria
Hi Andrew,
Thanks for getting back to me, I've just sent an email to info@stl-training.co.uk for the attention of your good self.
If you can help at all I would be very grateful
Tom
Thu 21 Oct 2010, 10:19Trainer Simon said...
RE: SUMIF statement with multiple criteria
Hi Tom,
Thank you for your response.
I am currently in the process of looking through the froum questions from last month.
What is the current status of your enquiry? Did you send a sample copy through to Andrew and have you received a response back from him?
Look forward to hearing from you.
Regards
Simon
Thu 21 Oct 2010, 12:02Delegate Tom said...
RE: SUMIF statement with multiple criteria
Hi Simon,
No I havent, I have tried with several people and they dont get back to me. Is it not doable?
I have just sent it again to info@stl-training.co.uk with the subject: FW: FAO Simon - SUMIF Issue
Thanks very much,
Tom
Thu 21 Oct 2010, 15:41Trainer Simon said...
RE: SUMIF statement with multiple criteria
Hi Tom,
Thank you for the file. Let me have a look tomorrow as I am not training.
Regards
Simon
Thu 28 Oct 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:Make a quick copy of a worksheetHold down the Ctrl key, then click and drag on a sheet tab to make a copy of that sheet. Though this process usefully copies the formats of the original sheet, note that any Range Names you have on the original sheet will be duplicated too. |
