SUMIFS Formula

[howard]

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
	A	B	C	D
1	Ageing > 40 Days		R326.28
2
3	Ageing	Value	% of Total
4		1572.997	8.14%
5	10-15	3745.451	19.39%
6	15-20	2482.914	12.85%
7	20-25	2703.909	14.00%
8	25-30	2089.743	10.82%
9	30-35	2298.29	11.90%
10	35-40	434.3407	2.25%
11	40-45	326.2813	1.69%
12	45-50	384.9833	1.99%
13	50-55	-7.37867	-0.04%
14	55-60	14.66667	0.08%
15	>60	3273.837	16.95%
16	Grand Total	19320.03	100.00%
17
Sheet1

 

[squirrel31]

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]

-7.37867 is meant to be negative?

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

 

[squirrel31]

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]

Thanks for the help, much appreciated

 

[squirrel31]

Glad I could help!

 

[barry houdini]

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]

The more you learn...

 

[howard]

Hi Barry

Thanks for the info, much appreciated