+ Reply to Thread
Results 1 to 3 of 3

Sumproduct Countif multiple criteria on second sheet

  1. #1
    Registered User
    Join Date
    05-30-2015
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    1

    Sumproduct Countif multiple criteria on second sheet

    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 that match across the rows 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:I518="Claim")--(Sheet1!J2:J518="Motor"))

    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sumproduct Countif multiple criteria on second sheet

    Try the fomula below

    Sumproduct(--(Sheet!I2:I518="Claim")*--(Sheet1!J2:J518="Motor"))

    I should use a pivot table instead.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,698

    Re: Sumproduct Countif multiple criteria on second sheet

    Probably easier to use COUNTIFS for this, e.g.

    =COUNTIFS(Sheet!I2:I518,"Claim",Sheet1!J2:J518,"Motor")

    You can add more conditions
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sumproduct or countif with multiple criteria
    By dralyagmas in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2010, 02:01 AM
  2. Multiple criteria countif or sumproduct
    By jmerre in forum Excel General
    Replies: 9
    Last Post: 09-16-2009, 03:09 PM
  3. Multiple criteria countif/sumproduct function
    By Cobbcouk in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-25-2006, 07:30 AM
  4. COUNTIF or SUMPRODUCT counting multiple criteria
    By Kim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] countif/sumproduct on multiple criteria
    By Phil.M in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2005, 02:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1