SUMPRODUCT equation based on multiples

[PaulyH]

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

 

[xld]

Try

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

 

[PaulyH]

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

 

[xld]

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

 

[PaulyH]

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

 

[xld]

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

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

 

[PaulyH]

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.

 

[Aladin Akyurek]

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

=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.

 

[PaulyH]

Thanks

How do I get it to refer?

 

[xld]

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.