# Help using formulas - conditional sumproduct???

#### rbeyer87

##### New Member
 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!!

### 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
Maybe:

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

</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))

</tbody>

<tbody>
</tbody>

#### Eric W

##### MrExcel MVP
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

</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))}

</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
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
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!

Last edited:

Replies
3
Views
124
Replies
4
Views
122
Replies
6
Views
125
Replies
2
Views
256
Replies
9
Views
81

### Forum statistics

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.

### Which adblocker are you using?

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