# Modifying formula SUMPRODUCT() and COUNT()

1. ## 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.

2. ## 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. ## 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

4. ## 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

5. ## Re: Modifying formula SUMPRODUCT() and COUNT()

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

Ctrl+Shift+Enter

6. ## 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

##### Users Browsing this Thread

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

#### 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