Results 1 to 21 of 21

SUMPRODUCT with COUNTIF to find unique entries

Threaded View

crzu SUMPRODUCT with COUNTIF to... 11-07-2012, 10:53 PM
FDibbins Re: SUMPRODUCT with COUNTIF... 11-07-2012, 11:04 PM
crzu Re: SUMPRODUCT with COUNTIF... 11-07-2012, 11:07 PM
crzu Re: SUMPRODUCT with COUNTIF... 11-08-2012, 09:10 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-09-2012, 02:54 AM
Fotis1991 Re: SUMPRODUCT with COUNTIF... 11-09-2012, 04:29 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-09-2012, 05:27 AM
Fotis1991 Re: SUMPRODUCT with COUNTIF... 11-09-2012, 05:32 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-09-2012, 05:39 AM
Fotis1991 Re: SUMPRODUCT with COUNTIF... 11-09-2012, 06:09 AM
Ron Coderre Re: SUMPRODUCT with COUNTIF... 11-09-2012, 10:14 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-11-2012, 03:56 AM
Fotis1991 Re: SUMPRODUCT with COUNTIF... 11-11-2012, 04:44 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-11-2012, 04:55 AM
sunflowers Re: SUMPRODUCT with COUNTIF... 11-11-2012, 05:54 AM
jason.b75 Re: SUMPRODUCT with COUNTIF... 11-11-2012, 07:22 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-13-2012, 10:17 PM
crzu Re: SUMPRODUCT with COUNTIF... 11-13-2012, 10:54 PM
icestationzbra Re: SUMPRODUCT with COUNTIF... 11-13-2012, 11:42 PM
crzu Re: SUMPRODUCT with COUNTIF... 11-14-2012, 01:03 AM
crzu Re: SUMPRODUCT with COUNTIF... 11-15-2012, 10:04 PM
  1. #1
    Forum Contributor
    Join Date
    09-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2013
    Posts
    103

    SUMPRODUCT with COUNTIF to find unique entries

    Attached is a simulation file using SUMPRODUCT AND COUNTIF.

    Formula: copy to clipboard
    =ROUNDUP(SUMPRODUCT(--(B2:B16=F3)/COUNTIF(A2:A16,A2:A16)),0)


    Customer ID : A2:A16
    Entries Month : B2:B16
    Lookup Month : F3

    Problem
    However if you look at the attached file, this formula does not return the correct answer.

    Current Solution
    I insert a new column (C) and to combine the text together.
    E.g. C2 = A2&B2

    Then I use the below formula,
    Formula: copy to clipboard
    =SUMPRODUCT(--(B2:B16=F3)/COUNTIF(C2:C16,C2:C16))


    I got the correct result, but...

    Question
    Is it possible to have one formula instead of inserting a new column?

    I tried
    Formula: copy to clipboard
    =SUMPRODUCT(--(B2:B16=F3)/COUNTIF(A2:A16&B2:B16,A2:A16&B2:B16))

    Formula: copy to clipboard
    =SUMPRODUCT(--(B2:B16=F3)/COUNTIF(INDEX(A2:A16&B2:B16,0),INDEX(A2:A16&B2:B16,0)))

    Formula: copy to clipboard
    =SUMPRODUCT(--(B2:B16=F3)/COUNTIF(SUMPRODUCT(--(A2:A16&B2:B16=A2:A16&F3)),SUMPRODUCT(--(A2:A16&B2:B16=A2:A16&F3))))


    All failed...

    Your advice would be greatly appreciated.
    Attached Files Attached Files
    Last edited by crzu; 11-09-2012 at 02:53 AM. Reason: Weird attachment is gone
    Click * to reward me...
    Thank you...

Thread Information

Users Browsing this Thread

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

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