Excel SUMIFS Question

amccarty

New Member
Hello,

I am trying to sum a revenue column, while using two SUMIFS criteria and I am clearly doing something incorrectly, HELP! I figured out how to count accounts (that fall within revenue tiers) using the SUMPRODUCT formula, but now I need to know the revenue total for that same range (see count summary below). So the range in question is B128:B2751 which is formatted as revenue and the Revenue total column is C45:C55.

I know this is incorrect, but here is what I've tried:

=sumifs(B128:B2754,">0,<=50")
=sumifs(B128:B2754, ">0", B128:B2754, "<=50")

Cheers,
Amber

<table width="484" border="0" cellpadding="0" cellspacing="0"><col style="width: 171pt;" width="228"> <col style="width: 86pt;" width="114"> <col style="width: 107pt;" width="142"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 171pt;" width="228" height="17">Revenue - by Tier</td> <td class="xl67" style="width: 86pt;" width="114">Count</td> <td class="xl67" style="width: 107pt;" width="142"> Revenue</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">\$0 </td> <td class="xl70" align="right">277</td> <td class="xl73"> \$ - </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$0 - \$50</td> <td class="xl72" align="right">1301</td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$50 to \$100</td> <td class="xl72" align="right">329</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$100 to 200</td> <td class="xl72" align="right">262</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$200 - \$333</td> <td class="xl72" align="right">111</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$333 to \$500</td> <td class="xl72" align="right">55</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$500 to \$1k</td> <td class="xl72" align="right">78</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$1k to \$2k</td> <td class="xl72" align="right">60</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$2k to \$5k</td> <td class="xl72" align="right">58</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$5k to \$10k</td> <td class="xl72" align="right">41</td> <td class="xl71">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">> \$10k</td> <td class="xl72" align="right">52</td> <td class="xl71">
</td> </tr> </tbody></table>

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Welcome to the boards

Excel Workbook
DEFG
20500-5061
35110051-100250
4101200101-200150
Sheet1
Excel 2007

Is this what you are looking to accomplish? If so the formulae are:
Cell Formulas
RangeFormula
G2=SUMIFS(\$B\$2:\$B\$8,\$B\$2:\$B\$8,">="&D2,\$B\$2:\$B\$8,"<="&E2)
G3=SUMIFS(\$B\$2:\$B\$8,\$B\$2:\$B\$8,">="&D3,\$B\$2:\$B\$8,"<="&E3)
G4=SUMIFS(\$B\$2:\$B\$8,\$B\$2:\$B\$8,">="&D4,\$B\$2:\$B\$8,"<="&E4)

HTH.

Hi,

I think GNB formulas are Ok, but maybe (if i understood correctly) the data range is B128:B2754 and the formulas should be placed on C45:C55.

HTH

M.

I figured it out, I think.....it appears I need my revenue ranges to be in two different cells, so I reformatted the worksheet to reflect that, and then tried:

=SUMIF(\$B\$129:\$B\$2757,">"&A46)-SUMIF(\$B\$129:\$B\$2757,">"&B46)

Which does total out correctly, unless there is another way to do this, such as using the AND function with SUMIFS to state the greater than and less than ranges?

Thanks for the help on this board, as I am sure to come back with more questions. I am not an Excel formula juggerknot!

Cheers,
Amber

Last edited:

Replies
0
Views
228
Replies
2
Views
1K
Replies
1
Views
568
Replies
0
Views
396
Replies
0
Views
1K

1,217,257
Messages
6,135,503
Members
449,944
Latest member
parag385

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.

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