Help with Sumifs and/or criteria

[dave_f]

Hi all,

I'm creating a report that uses pulldowns to let people select various groupings of products.

So e.g. it will have a pull down for state listing out all of our states, but then will also include groupings of states,

"west region"
"east region"
"all"

I would like to create a sumifs formula that in psuedocode looks like:

=sumifs(productdata[revenue],productdata[state],regiongrouping)

Where "regiongrouping" is whatever is needed to accomplish the grouping they've selected in the pulldown.

I tried creating the region grouping pulldown for them using data validation and pointing to a list of values. Then using a vlookup to pass values to sumif that accomplish what I want:

=sumifs(productdata[revenue],productdata[state],vlookup(SelectedRegionGrouping,regionlookdecode,2,false))

Where regionlookdecode is a table that looks like this:

State, DecodeValue
MN , MN,
ND, ND,
...
west region, ???
east region, ???
All, *

I can't figure out what to put in the table for the ??? to make it give me the groupings I want. Say west region was all states except ND and MN. It seems like I need to use some kind of AND and OR formulas but I can't get it going and can't find much documentation on the criteria syntax that helps.

Is there a way to do this with sumifs and vlookup? I'd rather not delve into array formulas if I can avoid it.

Thanks!

Dave

 

[Steve=True]

Sounds like you really need a sumproduct. When you use the full power of sumproduct, you can select multiple criteria easily and can do more than just a sumif like averages, counts, sums

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

See if this is what you are looking for

 

[dave_f]

Sounds like you really need a sumproduct. When you use the full power of sumproduct, you can select multiple criteria easily and can do more than just a sumif like averages, counts, sums

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

See if this is what you are looking for

Thanks for the help.

Do you think there's a way to get the criteria used in a sumproduct like that to be generated in a lookup table like I described above?

I'd like to avoid having to have a different formula for each one of the possible groupings because it's likely in that in the future we'll want to look at things by a new grouping.

I'm hoping in those cases to just add a row to the criteria lookup table and have it work in the pulldown without having to go in and write another section of sumproduct in the formulas to build up to the grouping.

 

[Steve=True]

Yes you can modify sumproduct formula to look at a cell value or a control or whatever.

you may want to post a sample file so that someone can help yo uwith the formulas.

 

[dave_f]

Yes you can modify sumproduct formula to look at a cell value or a control or whatever.

you may want to post a sample file so that someone can help yo uwith the formulas.

Thanks, I have a sample workbook built. How do I attach it? (sorry if this if a FAQ, I looked through the tacked posting thread and didn't see it).

 

[jasonb75]

Not sure that sumproduct will work in the way you want.

Can be done with named ranges and INDIRECT, Basic example below to show how.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD>az</TD><TD></TD><TD>az</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>ny</TD><TD></TD><TD>ny</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD>az</TD><TD></TD><TD>uk</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD>uk</TD><TD></TD><TD>fr</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD>ny</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">1</TD><TD>az</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD>fr</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>europe</TD><TD style="TEXT-ALIGN: right">14</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B10</TD><TD>{=SUM(SUMIFS(A2:A8,B2:B8,INDIRECT(A10)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!


</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD></TD></TR><TR><TD></TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD></TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD></TD><TD>az</TD><TD>=Sheet2!$D$2</TD></TR><TR><TD></TD><TD>ny</TD><TD>=Sheet2!$D$3</TD></TR><TR><TD></TD><TD>uk</TD><TD>=Sheet2!$D$4</TD></TR><TR><TD></TD><TD>fr</TD><TD>=Sheet2!$D$5</TD></TR><TR><TD></TD><TD>usa</TD><TD>=Sheet2!$D$2:$D$3</TD></TR><TR><TD></TD><TD>europe</TD><TD>=Sheet2!$D$4:$D$5</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[jasonb75]

As an alternative that would be easier to set up, some constistancy in criteria could work, for example, if all state abbreviations are 2 characters (please excuse my ignorance) and all other references are more than 2 characters then you could use something like

=sum(sumifs(productdata[revenue],productdata[state],if(len(SelectedRegionGrouping)=2,SelectedRegionGrouping,choose(match(SelectedRegionGrouping,{"East","West","all"},0),List of East states, List of West states, List of all states))))

I've listed regions in an array constant for example, but you could use a list in a cell range.