# 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.  Register To Reply

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  Register To Reply

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  Register To Reply

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  Register To Reply

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

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

Ctrl+Shift+Enter  Register To Reply

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  Register To Reply

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