SumIf only if value is above a certain number

**micheldun** · 11-05-2012, 02:04 PM

I have used SumIf to determine add numbers using the regular criteria, for example:
=sumif($A1:$A6,$A1:$A6,$B1:$B6) to return the sums of values in B that match the row criteria in A many times with great success.

Now I need to do something more complicated. I only want to sum the value in B if it is over a specific number, like 10. I have tried nesting an IF statement and it doesn't look like that is the way to go.

Thanks in advance for your help,

micheldun

**TheExcelphile** · 11-05-2012, 02:10 PM

Try this:

=sumif(B1:B6,">10")

Check out http://theexcelphile.com/learning-ex...summing-it-up/

**micheldun** · 11-05-2012, 03:43 PM

TheExcelphile-

Thank you for your quick response. I don't believe I explained my problem properly. The set of data I am adding looks like this:

Name Number
A 9
A 10
A 11
B 2
B 4
C 4
C 10

There are 5,000 rows so I can afford the time to write a formula for each new name. So, I know that if I use =sumif(A$2:A$7,A$2:A$7,B$2:B$7) it will return 29 for A, 6 for B, etc...
What I need it to return is 21 for A, 0 for B, 10 for C, etc... I tried putting sumif(B$2:B$7,">9") in the sumrange of the above formula and that did not work.

Thank you,
micheldun

**oeldere** · 11-05-2012, 03:54 PM

With a help column and a pivot table.

See the attached file.

**micheldun** · 11-05-2012, 04:00 PM

Thank you oeldere,

I am preparing this for a pivot table that already contains a lot of information, but I believe I can make this work.

**Haseeb Avarakkan** · 11-05-2012, 04:01 PM

Hello Micheldun,

If I am following you correctly, you can use SUMIFS.

=SUMIFS(B:B,A:A,"A",B:B,">9")

**micheldun** · 11-05-2012, 04:06 PM

Haseeb,

That with a little bit of modification, that is exactly what I am looking for. Thank you much!
=SUMIFS(L$2:L$4493,A$2:A$4493,A$2:A$4493,L$2:L$4493,">13")

micheldun