Sumifs to find values between [SOLVED]

[mr_hiboy]

Hi


I'm trying to cum the quantity of product I have between specific ranges. Been trying with Sumifs, but now thinking this may not be the best solution. Wanted to double check before I start trying something else.


I've explained on the attached. Thanks in advance for you help


https://dl.dropboxusercontent.com/u/9071274/Sumifs.xlsx

 

[Hui]

in P7, I would use:

=SUMIFS($C4:$N4,$C$7:$N$7,">="&P4,$C7:$N7,"<="&P5)


The value in Q7 = 46 is correct

46 = 12+16+8+10

F4, H4, I4 & J4

 

[NARAYANK991]

Hi Paul ,


Since you will be copying Hui's formula down and across , change it slightly to :


=SUMIFS($C$4:$N$4,$C7:$N7,">="&P$4,$C7:$N7,"<="&P$5)


Narayan

 

[mr_hiboy]

ah victim of poor sorting in the first place!


Thanks Hui, also using the & isnew to me, a great help too!


Thanks again

 

[Hui]

& simply means join and is only used with Strings

so "<="&P$5

means join <= and the value of cell P5

 

[mr_hiboy]

Yeah I use & instead of concatenate for other things but didn't realise I could use it for this purpose. A great tip.


thanks