Results 1 to 3 of 3

Thread: This can't be this hard - SUMPRODUCT

  1. #1
    Neophyte MustLoveCorgis's Avatar
    Join Date
    Jun 2020
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 Plus

    Angry This can't be this hard - SUMPRODUCT



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have several spreadsheets. One of them is called: Accessibility Advisory Board Monthly Numbers.xlsx. It has a number of named ranges in it, namely Recd (the date an item is rec'd) and Clear (the date an item is cleared). I am creating another sheet to form some reports. The value I need is how many items that came in on or before the end of the month aren't done (or are done after the end of the month)

    Now, I would normally use a nice COUNTIFS for this, but I need to be able to have this data even if Accessibility Advisory Board Monthly Numbers.xlsx is closed. So, if I understand correctly, I would use SUMPRODUCT.

    I am looking for the report to count how many items exist:
    Where Recd is before or equal to the end of the month (in F3)
    AND
    Where Clear is AFTER the end of the month (in F3).
    OR
    Where Clear is empty

    THIS gives me the right number.
    =COUNTIFS('AccessibilityAdvisory Board MonthlyNumbers.xlsx'!Recd,"<="&$F$3,'Accessibility Advisory BoardMonthly Numbers.xlsx'!Clear,">="&$F$3)+COUNTIFS('AccessibilityAdvisory Board Monthly Numbers.xlsx'!Recd,"<="&$F$3,'AccessibilityAdvisory Board Monthly Numbers.xlsx'!Clear,"=")

    But I can't get my head to convert it to SUMPRODUCT so that Accessibility Advisory Board Monthly Numbers.xlsx doesn't have to be open. I have a feeling this is a stupid easy fix, but I can't see it.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Not tested, but try

    Code:
    =SUMPRODUCT(--('AccessibilityAdvisory Board MonthlyNumbers.xlsx'!Recd<=$F$3), 
                 --('Accessibility Advisory BoardMonthly Numbers.xlsx'!Clear>=$F$3))
    +SUMPRODUCT(--('AccessibilityAdvisory Board Monthly Numbers.xlsx'!Recd<=$F$3),
                --('AccessibilityAdvisory Board Monthly Numbers.xlsx'!Clear=""))

  3. #3
    Neophyte MustLoveCorgis's Avatar
    Join Date
    Jun 2020
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 Plus
    Not quite, but you got me going in the right direction! For some reason, it was also counting all of the rows where Clear was blank, but where Recd was too. So I had to account for those. THANK YOU!

    =IFERROR(SUMPRODUCT(--('AccessibilityAdvisory Board MonthlyNumbers.xlsx'!Project<>""),--('Accessibility Advisory BoardMonthly Numbers.xlsx'!Recd<=$F$3),--('Accessibility Advisory Board MonthlyNumbers.xlsx'!Clear>=$F$3)),0)+IFERROR(SUMPRODUCT(--('Accessibility AdvisoryBoard Monthly Numbers.xlsx'!Project<>""),--('AccessibilityAdvisory Board Monthly Numbers.xlsx'!Recd<=$F$3),--('Accessibility AdvisoryBoard Monthly Numbers.xlsx'!Clear="")),0)

Posting Permissions

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