# SUMIFS Formula

#### howard

##### Well-known Member
I am tring to sum the values in Col B where the ageing is > 40 days, but my SUMIFS FORmula is giving me the incorrect answer. It should be 3666.108

It would be appreciated if someone can check and amend my formula

Excel Workbook
ABCD
1Ageing > 40 DaysR326.28
2
3AgeingValue% of Total
41572.9978.14%
510-153745.45119.39%
615-202482.91412.85%
720-252703.90914.00%
825-302089.74310.82%
930-352298.2911.90%
1035-40434.34072.25%
1140-45326.28131.69%
1245-50384.98331.99%
1350-55-7.37867-0.04%
1455-6014.666670.08%
15>603273.83716.95%
16Grand Total19320.03100.00%
17
Sheet1

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### squirrel31

##### New Member
Is the -7.37867 an error or is it meant to be negative?

Additionally, I would have made it 3992.3896 (Adding up B11:15). Finally, looking at your formula it is just pulling the value that will be in 40-45.

#### howard

##### Well-known Member
-7.37867 is meant to be negative?

How do I get my formula to pull the correct value?

#### squirrel31

##### New Member
Took a bit of time as I had to teach myself how to do it but here's your solution.

=SUM(IF(A5:A15={"40-45","45-50","50-55","55-60",">60"},B5:B15,0)) (Produces 3992.3896 upon testing for me)

Note - Insert it using Ctrl+Shift+Enter.

A helpful link that I used: When to use a SUM(IF()) array formula (Example 2 in your case)

#### howard

##### Well-known Member
Thanks for the help, much appreciated

#### barry houdini

##### MrExcel MVP
Your original attempt will work if you just wrap a SUM function around it, i.e.

=SUM(SUMIFS(B3:B35,A3:A35,{"40-45","45-50","50-55","55-60",">60"}))

That works because the SUMIFS function returns an array of results - one for each value in {"40-45","45-50","50-55","55-60",">60"} - so you need SUM to sum that array.

This version doesn't need CTRL+SHIFT+ENTER

#### squirrel31

##### New Member
The more you learn...

#### howard

##### Well-known Member
Hi Barry

Thanks for the info, much appreciated

Replies
4
Views
352
Replies
3
Views
342
Replies
0
Views
763
Replies
3
Views
554
Replies
6
Views
732

1,191,686
Messages
5,988,068
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back