+ Reply to Thread
Results 1 to 6 of 6

Modifying formula SUMPRODUCT() and COUNT()

  1. #1
    Registered User
    Join Date
    05-28-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    2

    Modifying formula SUMPRODUCT() and COUNT()

    Hello!

    This is my formula as of right now:

    HTML Code: 
    =SUMPRODUCT((Table1[Color]="Orange")*(Table1[EmployeeCard]="Orange")/(COUNTIFS(Table1[Color], "Orange", Table1[EmployeeCard], "Orange", Table1[Number],Table1[Number])+(Table1[Color]<>"Orange")+(Table1[EmployeeCard]<>"Orange")))
    As it is, it counts the number of distinct rows (based by Number) which has Color as "Orange" and EmployeeCard as "Orange". I'd like to modify this formula to also count the rows which have "Orange" in Color OR "Orange" in EmployeeCard.

    So while the actual count gives me 1 (one row) it should give me 2 (two rows).

    Any help appreciated!

    Note: Dynamic table, Excel 2010.
    Attached Files Attached Files
    Last edited by CouchWitcher; 05-28-2020 at 04:12 PM.

  2. #2
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    222

    Re: Modifying formula SUMPRODUCT() and COUNT()

    Why do you want to count row 4 but not 8?
    I do not understand your data set i'm afraid.

    Can you clarifiy?

    Thanks

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,110

    Re: Modifying formula SUMPRODUCT() and COUNT()

    G2=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(B2,Tableau1[Color]&Tableau1[EmployeeCard])),MATCH(Tableau1[Color]&Tableau1[EmployeeCard],Tableau1[Color]&Tableau1[EmployeeCard],0)),ROW(Tableau1[Color])-ROW(B2)+1),1))

    Control+shift+enter
    Last edited by CARACALLA; 05-28-2020 at 03:26 PM.

  4. #4
    Registered User
    Join Date
    05-28-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    2

    Re: Modifying formula SUMPRODUCT() and COUNT()

    Hello RaulSerg!

    You are totally right, I've made a mistake in my dataset. I'll edit it right now! Thank you for answering my question. In the Column Number, and there is the word "Orange" in either Color or EmployeeCard or both, i'd like the row to be counted. However, if the number is duplicated in many rows, and 1 or more of these rows have the word "Orange" in the appropriate column, it would be counted once.

    Let me edit everything! Sorry also english isn't my first language
    Last edited by CouchWitcher; 05-28-2020 at 03:41 PM.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Modifying formula SUMPRODUCT() and COUNT()

    Please try

    =COUNT(1/FREQUENCY(IF((Tableau1[Color]="Orange")+(Tableau1[EmployeeCard]="Orange"),Tableau1[Number]),Tableau1[Number]))

    Ctrl+Shift+Enter
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,110

    Re: Modifying formula SUMPRODUCT() and COUNT()

    New file

    G2=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(B2,Tableau1[Color]&Tableau1[EmployeeCard])),Tableau1[Number]),Tableau1[Number]),1))

    Control+shift+enter

+ 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 not giving correct count
    By mmccra2858 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-03-2017, 01:59 PM
  2. [SOLVED] SumProduct vs Count if formula
    By daisiedawn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2014, 04:53 PM
  3. Need help modifying a complex SUMPRODUCT search algorithm
    By BBL in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-01-2013, 03:02 PM
  4. Replies: 4
    Last Post: 02-14-2012, 02:47 AM
  5. Modifying a sumproduct formula to search for words in a cell
    By Weasel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2009, 02:51 PM
  6. Modifying A Sumproduct Formula
    By carl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2005, 03:25 PM
  7. Replies: 0
    Last Post: 08-28-2005, 10:54 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