# SUMPRODUCT equation based on multiples

#### PaulyH

##### New Member
Hi

I had some help on here to create the following formula which I'm now repeating in more elaborate forms. At the moment I am using the formula below but I need to get the bit in red in the second half of the formula to search on more than the one condition.

Currently it is searching a postcode abbreviation field and is picking up all those with 'BS' however I don't just want to search on BS. I would like to search on BS, BA, SN, TA, GL. I have tried putting different combination using commas and plus signs etc in but excel doens't seem to like the way I'm doing it.

=SUMPRODUCT(--(Sheet1!\$T\$3:\$T\$10000=A49),--(Sheet1!\$S\$3:\$S\$10000="BS"))

Paul

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try

=SUMPRODUCT((Sheet1!\$T\$3:\$T\$10000=A49)*(Sheet1!\$S\$3:\$S\$10000={"BS","BA","SN","TA","GL"}))

Unfortunately that doesn't work. It gives me the value '6' when it should be '61'.

Is it a pure count, or should it be summing another column as well?

I'm not sure what a pure count is. Basically I need the cell to display the number of cells in column T that have the value that is equal to cell A49 (which in this case is the text '15A') but only where the cell value in the adjacent cell in column S has one of the following values BS, BA, SN, TA, GL.

Hope that makes sense.

Thanks for your help

The formula does exactly that, and works fine in my tests.

Are you sure that there are no extra spaces in those cells.

Unfortunately it isn;t working correct for me.

Here is a snap shot:

As you can see the forula you supplied is in the highlighted cell and has the value of '10' (more info has been added from earlier when I said this was '6'

The cell to the left has the formula
=SUMPRODUCT(--(Sheet1!\$T\$3:\$T\$10000=A50),--(Sheet1!\$S\$3:\$S\$10000="BS"))

So if that is the count of just those with 'BS' then the cell next to it should have a value of at least '50'

Here is also a snap shot of the data from which the data is being drawn:

As you can see their are lost of different values in the column 'S' (more than I can fit on this one page. And as explained I need to count all those that contain the values listed above.

Hi

I had some help on here to create the following formula which I'm now repeating in more elaborate forms. At the moment I am using the formula below but I need to get the bit in red in the second half of the formula to search on more than the one condition.

Currently it is searching a postcode abbreviation field and is picking up all those with 'BS' however I don't just want to search on BS. I would like to search on BS, BA, SN, TA, GL. I have tried putting different combination using commas and plus signs etc in but excel doens't seem to like the way I'm doing it.

=SUMPRODUCT(--(Sheet1!\$T\$3:\$T\$10000=A49),--(Sheet1!\$S\$3:\$S\$10000="BS"))

Paul
Code:
``````=SUMPRODUCT(
--(Sheet1!\$T\$3:\$T\$10000=A49),
--ISNUMBER(MATCH(Sheet1!\$S\$3:\$S\$10000,List,0)))``````

where List refers to a range that lists BS, BA, SN, TA, GL, each one in a cell of its own.

This formula produces a count where T-range is equal to A49 and S-range is equal to one of the values in List.

Thanks

How do I get it to refer?

Unfortunately it isn;t working correct for me.

Here is a snap shot:

As you can see the forula you supplied is in the highlighted cell and has the value of '10' (more info has been added from earlier when I said this was '6'

The cell to the left has the formula
=SUMPRODUCT(--(Sheet1!\$T\$3:\$T\$10000=A50),--(Sheet1!\$S\$3:\$S\$10000="BS"))

So if that is the count of just those with 'BS' then the cell next to it should have a value of at least '50'

Here is also a snap shot of the data from which the data is being drawn:

As you can see their are lost of different values in the column 'S' (more than I can fit on this one page. And as explained I need to count all those that contain the values listed above.

Those images are far too unclear.

I have just created that data with 25 matches and it counts 25 perfectly well.

Replies
0
Views
345
Replies
5
Views
149
Replies
3
Views
144
Replies
1
Views
193
Replies
3
Views
506

1,207,260
Messages
6,077,351
Members
446,279
Latest member
hoangquan2310

### 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?

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