Excel SUMIFS Question

[amccarty]

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>

 

[GNB]

Welcome to the boards

Excel Workbook
	D	E	F	G
2	0	50	0-50	61
3	51	100	51-100	250
4	101	200	101-200	150
Sheet1

Excel 2007

Is this what you are looking to accomplish? If so the formulae are:

Cell Formulas
Range	Formula
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]

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]

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