Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 30th 15, 06:07 AM
Junior Member
 
First recorded activity by ExcelBanter: May 2015
Posts: 1
Default 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   Report Post  
Old 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
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMPRODUCT or COUNTIF - Multiple Criteria J Fraggis Excel Worksheet Functions 2 March 3rd 09 04:51 PM
COUNTIF or SUMPRODUCT with multiple criteria ERICinLA77 Excel Worksheet Functions 1 November 25th 08 07:07 PM
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter Greg in CO[_2_] Excel Worksheet Functions 0 September 18th 08 05:51 PM
Sumproduct as Countif multiple criteira I'm Still Here Excel Worksheet Functions 2 March 18th 06 08:17 PM
countif/sumproduct on multiple criteria Phil.M Excel Worksheet Functions 2 April 1st 05 06:48 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017