98.8% 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 / Sumifs with multiple criteria and ranges | Excel forum
Sumif / Sumifs with multiple criteria and ranges | Excel forum
Resolved · High Priority · Version 2019
Wed 24 Mar 2021, 11:22Delegate Janet said...
Janet has attended:
Excel Advanced course
Excel Intermediate course
PowerPoint Intermediate Advanced course
Sumif / Sumifs with multiple criteria and ranges
I have tried various options but cannot get the correct result.
I need a total in cell q253. I want to sum all values in Q3 to Q247 using these criteria:-
If Cell d3 to d247 states "1" (Other options 0 or 2)
And
Cells C3 TO C247 state "In Progress" and " Planned"
So - 3 criteria to get my result.
Below formula gives me total that have "1" Column D , how do I add in the criteria to search in C3 to C247 to also look for Planned and In Progress? I tried to add in the next argument for 2nd range but it doesn't work.
I have tried to use Sumifs with and without an array also. When I think I have it right I get a return of 0. I did make sure to put the range in the correct position dependent on Sumif vs Sumifs
=SUM(SUMIF(D3:D247,{"1","Planned","In Progress"},Q3:Q247))
If this makes any sense at all can you please advise where I am going wrong. Thanks
Wed 24 Mar 2021, 15:27
Edited on Wed 24 Mar 2021, 15:29Trainer Jens said...
RE: Sumif / Sumifs with multiple criteria and ranges
Hi Janet,
Thank you for the forum question.
If I understand you right the formula below should do it:
=SUMIFS(Q3:Q247,D3:D247,1,C3:C247,"Planned")+SUMIFS(Q3:Q247,D3:D247,1,C3:C247,"In Progress")
If the number 1 is a criteria you shouldn't use "1".
If Cell d3 to d247 states "1" (Other options 0 or 2)
And
I assume you mean "In Progress" or " Planned"
Cells C3 TO C247 state "In Progress" and " Planned"
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 24 Mar 2021, 15:48Delegate Janet said...
RE: Sumif / Sumifs with multiple criteria and ranges
Hi Jens,
Thank you that works, however if I try to drag to next column it is bring across the same answer, I think I need to add $ but can't see where. Also I tried to drag down a row so I could look up the results with crteria 2 in place of 1 from column d , I tried to just change the 1's to 2's in the formula but nothing changed. Sorry to be a pain but I have tried to do it myself.
Regards
Janet
Wed 24 Mar 2021, 16:14Trainer Jens said...
RE: Sumif / Sumifs with multiple criteria and ranges
Hi Janet,
When you write copy to next column. If you mean that you want Excel to sum values in column R and you still want to find the criteria (1, "Planned", "In Progress") in column D and C. The formula should look like this. And the you should be able to copy it down and just replace 1 with 2.
=SUMIFS(Q$3:Q$247,$D$3:$D$247,1,$C$3:$C$247,"Planned")+SUMIFS(Q$3:Q$247,$D$3:$D$247,1,$C$3:$C$247,"In Progress")
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 24 Mar 2021, 16:27Delegate Janet said...
RE: Sumif / Sumifs with multiple criteria and ranges
Thank you so much Jens, I was close but confused myself. It all works now.
Very grateful.
Regards
Janet
Wed 24 Mar 2021, 17:03Trainer Jens said...
RE: Sumif / Sumifs with multiple criteria and ranges
You are welcome. I am happy to help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 31 Mar 2021: 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:The dual nature of toolbar buttonsMany toolbar buttons are dual purpose, though the two purposes are often linked in some way. For example, Align Left aligns a cell's contents to the left of the cell. However, hold down Shift and press the Align Left button: Excel aligns the cell contents to the right. |
