Excel SUMIFS Question

amccarty

New Member
Joined
Mar 7, 2011
Messages
11
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")

Thank you for your help.

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GNB

Board Regular
Joined
Aug 12, 2009
Messages
82
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,868
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

amccarty

New Member
Joined
Mar 7, 2011
Messages
11
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:

Forum statistics

Threads
1,141,849
Messages
5,708,965
Members
421,601
Latest member
Garlo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top