Janet has attended:
Excel Advanced course
Excel Intermediate course
PowerPoint Intermediate Advanced course
Project Management - Framework & Processes 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
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
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
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
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
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
