Sum or Countif over Multiple Columns
 

Hi
Currently I am using Sumproduct to count column B "NU" and column F "D" but
now I also need it to look at column I (which contains the value for that
row) and then calculate that figure.

e.g. using the Sumproduct column B "NU" and column F "D" there are 34
entries so it should calculated each entry (expected value of £45,000).

Can anyone solve this problem for me?????

Sum or Countif over Multiple Columns
 

=SUMPRODUCT(--(B2:B200="NU"),--(F2:F200="D"),I2:I200)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"AndyO_UK" wrote in message
...
Hi
Currently I am using Sumproduct to count column B "NU" and column F "D"
but
now I also need it to look at column I (which contains the value for that
row) and then calculate that figure.

e.g. using the Sumproduct column B "NU" and column F "D" there are 34
entries so it should calculated each entry (expected value of £45,000).

Can anyone solve this problem for me?????

Sum or Countif over Multiple Columns
 

I think this may need an Array formula. If you're not familiar with them ,
read the XL help section. In the meantime a solution might be


Sum(('<sheetname!$B$2:$B$65536="NU")*('<Sheetname !$F$2:$F$65536="D")*('<Sheetname!$I$2:$I$65536 ))

If the formula is on the same sheet as the data, you wont need '<Sheetname!

You would need to enter this formula using the <CTRL <SHIFT <ENTER key
simultaneously.

When you do this, the formula in the Edit bar should look like this:
{Sum(('<sheetname!$B$2:$B$65536="NU")*('<Sheetnam e!$F$2:$F$65536="D")*('<Sheetname!$I$2:$I$65536) )}

What the formula is saying is in Col B where you find "NU" and i Col F where
you find "D" then add the corresponding value on Col I. The formula repeats
this for each row expressed in the formula.

Hope this makes a bit of sense!

John

"AndyO_UK" wrote:

Hi
Currently I am using Sumproduct to count column B "NU" and column F "D" but
now I also need it to look at column I (which contains the value for that
row) and then calculate that figure.

e.g. using the Sumproduct column B "NU" and column F "D" there are 34
entries so it should calculated each entry (expected value of £45,000).

Can anyone solve this problem for me?????

Sum or Countif over Multiple Columns
 

Many thanks Bob :-)

Problem solved.

"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B200="NU"),--(F2:F200="D"),I2:I200)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"AndyO_UK" wrote in message
...
Hi
Currently I am using Sumproduct to count column B "NU" and column F "D"
but
now I also need it to look at column I (which contains the value for that
row) and then calculate that figure.

e.g. using the Sumproduct column B "NU" and column F "D" there are 34
entries so it should calculated each entry (expected value of £45,000).

Can anyone solve this problem for me?????