+ Reply to Thread
Results 1 to 6 of 6

SumProduct and CountIf issues

  1. #1
    Registered User
    Join Date
    11-05-2020
    Location
    London, England
    MS-Off Ver
    MS Office 10
    Posts
    7

    SumProduct and CountIf issues

    Hi All,

    Wondering if you can help me, I have a table like the below:

    Area Fruit
    NW Apples
    NW Apples
    SS Pear
    NE Apples
    SS Orange
    JP Grape

    I have two fields like the below:

    Area Fruit
    NW Apples

    I want to know the number of Apples in NW for example, when I use the formula below:

    =(SUMPRODUCT((Data!C:C=A2)*(Data!F:F=B2))), I get 2, my issue is, when I remove apples from the fruit field above and just want to see the number of fruits in NW, I get 0 or 1. How do I go about this issue where if a fruit field is empty, just return the result for the area?

    If this makes sense.

    Thank you

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: SumProduct and CountIf issues

    You are showing what looks like one column of Area Fruit and one cell with NW Apples in it. But your sumproduct formula shows columns C, F and cells A2 and B2.
    I cannot replicate your logic in a workbook on my own, can you post a sample workbook per the yellow banner at the top of the post so we can see what you have and what you want?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: SumProduct and CountIf issues

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    When you have two conditions, you are using the multiply operand ( * ) to convert the boolean values to numeric values. If you have only one condition, you have to use a different numeric operation ... in this case, a double negative ( -- )

    You don't actually need all the brackets:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that, although current versions of Excel allow full column references in SUMPRODUCT, it is very bad practice. As you are doing matrix multiplication of over a million rows, it will have a serious impact on performance.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: SumProduct and CountIf issues

    If I understand you correctly, I used an nested if statement. IF Area is blank then it counts the number of fruits that match B2, IF Fruit is blank then it counts the number that area is listed, else it does what you started with.

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: SumProduct and CountIf issues

    You can make your answers more readable in-cell too if you wish using that IF formula.

    e.g.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: SumProduct and CountIf issues

    Try this ARRAY formula

    =SUM(($C$2:$C$7=A2)*(IF(B2="",1,($F$2:$F$7=B2))))

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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. [SOLVED] SUMPRODUCT formula issues
    By antexity in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2015, 08:59 AM
  2. SUMPRODUCT or COUNTIF issues
    By reidos2800 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-25-2013, 05:14 PM
  3. sumproduct issues
    By Dyakin in forum Excel General
    Replies: 6
    Last Post: 03-29-2012, 10:03 AM
  4. should I use countif or sumproduct to return # of nameA's priority 2 issues
    By StressedOutMom in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-11-2012, 05:59 PM
  5. Issues with Sumproduct
    By Chareth Cutestory in forum Excel General
    Replies: 6
    Last Post: 09-02-2011, 06:08 PM
  6. sumproduct issues
    By tweety127 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2008, 11:38 AM
  7. SUMPRODUCT issues
    By Ashlynn Grace in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-26-2006, 05:00 PM

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