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
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
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
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
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
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
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
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
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
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
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
