How "sum" text in cells across rows – SUM, IF, FREQUENCY?

[somanyqs]

My head is starting to hurt...feel like this one should be easy but I can't figure it out!

I have a list rows with companies, purchase dates, and what widget they purchased.
And also a flag for if the row was a Type1 deal or a Type2 deal (simple Yes/No) in different columns.

What I am trying to do is to add a new column (F) that says (Yes/No) whether that company has ever had BOTH Type1 or Type2 deals at any time. So it isn't specific to a particular row, I want to look across all of the rows with the company name in column A and see if they have purchased both (Yes) a Type1 and Type2 deal, at any time.

Apologies for not pasting anything in - I'm on a Mac and can't figure out how :-O

So both IBM and Nike have both purchased Type1 and Type2 deals (could be on the same date or not, doesn't matter), so I want to put a "Yes" in column F for every row with IBM and Nike.
FedEx and Oracle both get "No" in all column F cells.


Date Widget Type1 Type2 Type 1 and 2 ever
IBM 1/2013 Widget1 Yes No Yes
IBM 1/2013 Widget2 No Yes Yes
IBM 2/2013 Widget3 No Yes Yes
FedEx 1/2013 Widget1 Yes No No
FedEx 2/2013 Widget3 No No No
FedEx 2/2013 Widget4 Yes No No
Oracle 2/2013 Widget1 No Yes No
Oracle 3/2013 Widget3 No No No
Nike 2/2013 Widget1 No No Yes
Nike 1/2013 Widget1 Yes Yes Yes
Nike 2/2013 Widget1 No No Yes

Phew!! Hopefully that makes sense...this is pretty tricky to me. thanks all!

 

[Matty]

Try:

=IF((COUNTIFS(A$2:A$12,A2,D$2:D$12,"Yes")>0)+(COUNTIFS(A$2:A$12,A2,E$2:E$12,"Yes")>0)=2,"Yes","No")

In F2, and copy down.

Matty

 

[ArthriticPanda]

What I am trying to do is to add a new column (F) that says (Yes/No) whether that company has ever had BOTH Type1 or Type2 deals at any time. So it isn't specific to a particular row, I want to look across all of the rows with the company name in column A and see if they have purchased both (Yes) a Type1 and Type2 deal, at any time.

Hi,

Matty has provided the answer you requested...but I've gone a slightly different way as I take it from your explanation that all you require to know is which company has purchased either type 1 and 2 at any time?

I've used a validation list so you can choose the company and the formula will provide the answer...well that's the theory

Create a validation list with all the Companies and use the following formula:

=IF(AND(SUMPRODUCT(--(A2:A12=H2)*(D2:D12="Yes")),SUMPRODUCT(--(A2:A12=H2)*(E2:E12="Yes"))),"Yes","No")

where cell H2 holds the value of the company when picked from the list.

AP

 

[somanyqs]

You guys rule
Matty +1
ArthriticPanda +1

Getting Excel work completed in time for upcoming weekend +1

 

[Aladin Akyurek]

Hi,

Matty has provided the answer you requested...but I've gone a slightly different way as I take it from your explanation that all you require to know is which company has purchased either type 1 and 2 at any time?

I've used a validation list so you can choose the company and the formula will provide the answer...well that's the theory

Create a validation list with all the Companies and use the following formula:

=IF(AND(SUMPRODUCT(--(A2:A12=H2)*(D2:D12="Yes")),SUMPRODUCT(--(A2:A12=H2)*(E2:E12="Yes"))),"Yes","No")

where cell H2 holds the value of the company when picked from the list.

AP

--(A2:A12=H2)*(D2:D12="Yes")

is not wel-formed. Follow rather something like...

Either:

--(A2:A12=H2),--(D2:D12="Yes")

Or:

(A2:A12=H2)*(D2:D12="Yes")

Or:

(A2:A12=H2)+0,(D2:D12="Yes")+0

instead.