sumif sumifs multiple

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Sumif / Sumifs with multiple criteria and ranges | Excel forum

# Sumif / Sumifs with multiple criteria and ranges | Excel forum

Resolved · High Priority · Version 2019

Janet has attended:
Excel Intermediate 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

Edited on Wed 24 Mar 2021, 15:29

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

Wed 31 Mar 2021: Automatically marked as resolved.

Training courses

Training information:

 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 What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### The dual nature of toolbar buttons

Many 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.
You may respond: So what? Well, you can reduce the number of buttons on your toolbar to make your screen less cluttered and allow more room for, perhaps, some of your own commands. After all, what's the point of an Align Right button when Shift+Align Left does the same thing?

View all Excel hints and tips