Remember Me?

#1
May 30th 15, 06:07 AM
 Junior Member First recorded activity by ExcelBanter: May 2015 Posts: 1
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!

Last edited by Nean : May 30th 15 at 06:40 AM

#2
May 30th 15, 01:07 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post J Fraggis Excel Worksheet Functions 2 March 3rd 09 04:51 PM ERICinLA77 Excel Worksheet Functions 1 November 25th 08 07:07 PM Greg in CO[_2_] Excel Worksheet Functions 0 September 18th 08 05:51 PM I'm Still Here Excel Worksheet Functions 2 March 18th 06 08:17 PM Phil.M Excel Worksheet Functions 2 April 1st 05 06:48 AM

All times are GMT +1. The time now is 06:07 PM.