Help using formulas - conditional sumproduct???

rbeyer87

New Member
Joined
Jun 28, 2017
Messages
12
Small
Medium
Large
Blue
1
1
2
Red
1
Green
2
Orange
1
Purple
1
2
Yellow
2
1
1

<tbody>
</tbody>
I have a worksheet that I need some help with. I need formulas to accomplish a count. Basically I have two columns outside of this range. One column is labeled "Light" and lists underneath it all of the light colors...so Orange, Purple and Yellow. A second column is labeled "Dark" and lists underneath it all of the dark colors...Blue, Red and Green. (Work with me I'm totally making up all of this) What I want to know is how many Light and Dark colors I have in each size.

So I need six different cells to have formulas counting all of the different combinations. First cell would be a formula to show the number of Small and Dark combinations. Second cell would show Medium and Dark, etc. It seems like it should be straightforward but I'm struggling to come up with the right formula. I feel like it needs a sumproduct, but I just can't get it to work. I would really appreciate anyone's help. Also, data is not sorted in any particular order and would need to stay that way. Thanks in advance!!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Maybe:

ABCDEFGHIJKL
1SmallMediumLargeLightDarkSmallMediumLarge
2Blue112OrangeBlueLight314
3Red1PurpleRedDark142
4Green2YellowGreen
5Orange1
6Purple12
7Yellow211

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
J2=SUMPRODUCT($B$2:$D$7*(COUNTIF(INDEX($F$2:$G$10,0,MATCH($I2,$F$1:$G$1,0)),$A$2:$A$7)>0)*($B$1:$D$1=J$1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
I had another thought. If you define your Light and Dark ranges as defined Names, you can simplify the formula further:

ABCDEFGHIJKL
1SmallMediumLargeLightDarkSmallMediumLarge
2Blue112OrangeBlueLight314
3Red1PurpleRedDark142
4Green2YellowGreen
5Orange1
6Purple12
7Yellow211

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Array Formulas
CellFormula
J2{=SUMPRODUCT($B$2:$D$7*(COUNTIF(INDIRECT($I2),$A$2:$A$7)>0)*($B$1:$D$1=J$1))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Where you define F2:F10 as "Light" and G2:G10 as "Dark".
 

rbeyer87

New Member
Joined
Jun 28, 2017
Messages
12
I used the first option and it worked great, probably would not have figured that out on my own, so thank so much. I did not use the second version, even though it seemed to be more efficient, because there will be other users of the spreadsheet who do not have experience with arrays and I was afraid that may cause issues down the road. Thanks so much again for your help!! Ron
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Somewhat counter-intuitively, I think the second formula was actually a bit less efficient, because of the INDIRECT function. It was just a bit shorter, and I thought possibly a bit easier to understand, but if someone doesn't have experience with Names, it would be worse.

At the risk of muddying the waters, here's another one - I got the idea for this from another post today:

J2: =SUM(SUMIF($A:$A,INDEX($F$2:$G$10,0,MATCH($I2,$F$1:$G$1,0)),B:B))
with Control+Shift+Enter.

It's not quite as robust as the first one though, it requires the headings in JKL to be the same as BCD.

In any case, glad to help! :cool:
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,308
Messages
5,623,890
Members
416,000
Latest member
Sovereign maphoso

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