Countif and Sumproduct Multiple Criteria and Sheets
 

I have a worksheet in which a want to take the data from three columns and put the total number of that data into a new sheet. I am using Excel 2010 and there are 517 rows of data within the 3 columns.
For example:
Each row in column 1 contains either claim or policy
Each row in column 2 contains either home or motor
Each row in column 3 contains either internal or external

So I want the total number of "Motor" "Claim" and "Internal" from the three columns for a seperate sheet.

What formula can be used for this?

I tried this formula to get the data from two columns but it keeps coming up as 0: Sumproduct(--(Sheet!I2:I124="Claim")--(Sheet1!J2:J124="Motor"))

Thanks!

Countif and Sumproduct Multiple Sheets
 

On Sat, 30 May 2015 06:07:59 +0100, Nean wrote:


I have a worksheet in which a want to take the data from three columns
and put the total number of that data into a new sheet. I am using Excel
2010 and there are 517 rows of data within the 3 columns.
For example:
Each row in column 1 contains either claim or policy
Each row in column 2 contains either home or motor
Each row in column 3 contains either internal or external

So I want the total number of "Motor" "Claim" and "Internal" from the
three columns for a seperate sheet.

What formula can be used for this?

I tried this formula to get the data from two columns but it keeps
coming up as 0:
Sumproduct(--(Sheet!I2:I124="Claim")--(Sheet1!J2:J124="Motor"))

Thanks!


You did not copy/paste your formula into this post, so it is difficult to tell what is a typo and what is a logic problem.

In particular, it is unlikely that you have a worksheet named merely "Sheet" and not "Sheet1"
There is no comma between the two arguments.

I would suggest
Correct the sheet name for the "Claim" argument if necessary.
Either insert a comma between the two arguments; or multiply them:

=SUMPRODUCT(--(Sheet1!I2:I124="Claim"),--(Sheet1!J2:J124="Motor"))

or

=SUMPRODUCT((Sheet1!I2:I124="Claim")*(Sheet1!J2:J1 24="Motor"))

If that is not the problem, you'll need to provide accurate data as to the formulas you are using, and the nature of the contents of the cells you are referencing