+ Reply to Thread
Results 1 to 3 of 3

Struggling with SUMPRODUCT

  1. #1
    Registered User
    Join Date
    09-10-2010
    Location
    Redditch, England
    MS-Off Ver
    Excel 2010
    Posts
    35

    Struggling with SUMPRODUCT

    I have a spreadsheet that gives me a list of dealers, customer names and dates.
    I am trying to calculate how many customers there are per dealer per month.

    I have attached an example spreadsheet.

    Dealer Name is in column A and date is in column E. However, I have converted the date into text in column E by using TEXT(cell,"mmm yyyy")

    I have created a report to show dealer names down column F and Dates along row 1, again I have converted the dates in row 2 into TEXT in column 1

    So I have used sumproduct and my formula is =SUMPRODUCT(($A$2:$A$5500=F3)*($E$2:$E$5075=$I$1))

    However, I am getting #N/A

    I don't understand why. I hope I have explained myself properly
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Struggling with SUMPRODUCT

    Hi,

    in sumproduct ranges must have same extension

    =SUMPRODUCT(($A$2:$A$5500=F3)*($E$2:$E$5500=$I$1))

    Hope it helps
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: Struggling with SUMPRODUCT

    You could try this instead:

    =COUNTIFS($A$2:$A$5102,F3,$E$2:$E$5102,I$1)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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] Struggling in writing sumproduct formula in excel noclamenture format
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-09-2016, 11:09 AM
  2. Struggling with SUMPRODUCT when matching "time between" values
    By designergav in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2015, 01:07 PM
  3. [SOLVED] I am struggling with sumproduct, pls help
    By edwardlobo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-25-2013, 08:19 AM
  4. Struggling with IF
    By KClark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2011, 04:37 AM
  5. struggling
    By Tomas mcD in forum Excel General
    Replies: 1
    Last Post: 03-07-2011, 08:25 AM
  6. Struggling with sumproduct
    By Angel160 in forum Excel General
    Replies: 4
    Last Post: 01-03-2008, 07:08 AM
  7. [SOLVED] Help, I'm Struggling!
    By Fybo in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-04-2005, 04:06 PM

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