sumifs returning 0 instead of value

[Mr2017]

Hi

I'm trying to write a SUMIFS formula that returns the Quantity total of the ItemSKU numbers in the 10000 range in the data below.

The data starts in cell A4 ie CustomerID is in cell A4 and UnitPrice is in cell D4.

I've tried two different formulae, but neither of them worked.

Does anyone know how I should amend the formula, please?

Version 1 of the formula I wrote was =SUMIFS($C$5:$C$24,$B$5:$B$24,">=10000,<=20000")

And version 2 was =SUMIFS($C$5:$C$24,$B$5:$B$24,">=10000"&"<=20000")

Thanks in advance!

[TABLE="width: 412"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]CustomerID[/TD]
[TD]ItemSKU[/TD]
[TD]Quantity[/TD]
[TD]UnitPrice[/TD]
[/TR]
[TR]
[TD]8725801[/TD]
[TD]12345[/TD]
[TD]100[/TD]
[TD]10.5[/TD]
[/TR]
[TR]
[TD]8825268[/TD]
[TD]23456[/TD]
[TD]22[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]8048929[/TD]
[TD]58910[/TD]
[TD]93[/TD]
[TD]17.5[/TD]
[/TR]
[TR]
[TD]8032245[/TD]
[TD]11121[/TD]
[TD]21[/TD]
[TD]22.5[/TD]
[/TR]
[TR]
[TD]8147770[/TD]
[TD]31415[/TD]
[TD]12[/TD]
[TD]17.5[/TD]
[/TR]
[TR]
[TD]8836323[/TD]
[TD]16171[/TD]
[TD]16[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]8286399[/TD]
[TD]81920[/TD]
[TD]40[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]8243640[/TD]
[TD]21222[/TD]
[TD]21[/TD]
[TD]21.89[/TD]
[/TR]
[TR]
[TD]8200881[/TD]
[TD]32425[/TD]
[TD]1[/TD]
[TD]2.0[/TD]
[/TR]
[TR]
[TD]8158122[/TD]
[TD]26272[/TD]
[TD]6[/TD]
[TD]89.0[/TD]
[/TR]
[TR]
[TD]8115363[/TD]
[TD]82930[/TD]
[TD]4[/TD]
[TD]50.0[/TD]
[/TR]
[TR]
[TD]8072604[/TD]
[TD]31323[/TD]
[TD]33[/TD]
[TD]38.3[/TD]
[/TR]
[TR]
[TD]8029845[/TD]
[TD]33435[/TD]
[TD]0[/TD]
[TD]50.8[/TD]
[/TR]
[TR]
[TD]7987086[/TD]
[TD]36373[/TD]
[TD]10[/TD]
[TD]54.7[/TD]
[/TR]
[TR]
[TD]7944327[/TD]
[TD]83940[/TD]
[TD]0[/TD]
[TD]58.7[/TD]
[/TR]
[TR]
[TD]7901567[/TD]
[TD]41424[/TD]
[TD]21[/TD]
[TD]62.7[/TD]
[/TR]
[TR]
[TD]7858808[/TD]
[TD]34445[/TD]
[TD]26[/TD]
[TD]66.6[/TD]
[/TR]
[TR]
[TD]7816049[/TD]
[TD]46474[/TD]
[TD]0[/TD]
[TD]70.6[/TD]
[/TR]
[TR]
[TD]7773290[/TD]
[TD]84950[/TD]
[TD]37[/TD]
[TD]74.5[/TD]
[/TR]
[TR]
[TD]7730531[/TD]
[TD]51525[/TD]
[TD]0[/TD]
[TD]78.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

The syntax for the SUMIFS should be:

Book1
	A	B	C	D	E	F
4	CustomerID	ItemSKU	Quantity	UnitPrice
5	8725801	12345	100	10.5		137
6	8825268	23456	22	2.5
7	8048929	58910	93	17.5
8	8032245	11121	21	22.5
9	8147770	31415	12	17.5
10	8836323	16171	16	20
11	8286399	81920	40	8.75
12	8243640	21222	21	21.89
13	8200881	32425	1	2
14	8158122	26272	6	89
15	8115363	82930	4	50
16	8072604	31323	33	38.3
17	8029845	33435	0	50.8
18	7987086	36373	10	54.7
19	7944327	83940	0	58.7
20	7901567	41424	21	62.7
21	7858808	34445	26	66.6
22	7816049	46474	0	70.6
23	7773290	84950	37	74.5
24	7730531	51525	0	78.5
Sheet474

Cell Formulas
Range	Formula
F5	=SUMIFS($C$5:$C$24,$B$5:$B$24,">=10000",$B$5:$B$24,"<=20000")

 

[Mr2017]

Ok, that's great - that worked! Thank you jtakw!

 

[jtakw]

You're very welcome.