If, And, Countif??

[Wikster7]

Hi there,

Having an issue combining if, and, & countif formula. Not sure if this is the most efficient way to execute what I'm trying to do.

Basically, I'm trying to do the following, which is based on a couple conditions:

1)If there is any value in E19 and there is a 'W' in E8, then output in C19 will be 1, which would be added up and divided by a number in B4.
2)If there is any value in E19 and there is a 'L' in E8, then output in D19 will be 1, which would be added up and divided by a number in B4.

My other issue that data that I am using skips columns, ei. E19, G19, I19, K19, etc. and I only know how to deal with data that is in a continuous stream.

I was having trouble attached the spread sheet, so I've attached a snapshot.

Thanks for your help in advance... I really appreciate it!

-Wikster7

tracker.PNG

[eibi]

You can paste in C19:

=SUMPRODUCT(($E$8:$N$8="W")*(E$18:N$18="I")*($E19:$N19)^0)/$B$4

and in D19:

=SUMPRODUCT(($E$8:$N$8="L")*(E$18:N$18="I")*($E19:$N19)^0)/$B$4

drag to fill

[Wikster7]

Hi eibi,

Thanks for the formulas! They work clients 1 to 5, but once I apply the formulas to clients that only showed up for some deals, I get this #NUM!

[eibi]

Yes -- I see the problem. I didn't allow for blanks.

Try this instead:

=SUMPRODUCT(($E$8:$N$8="W")*(E$18:N$18="I")*($E19:$N19<>0))/$B$4

and

=SUMPRODUCT(($E$8:$N$8="L")*(E$18:N$18="I")*($E19:$N19<>0))/$B$4

--------------------------

*edit:

Or - if you potentially want to include the value of 0 in your sum:

=SUMPRODUCT(($E$8:$N$8="W")*(E$18:N$18="I")*($E19:$N19<>""))/$B$4

and

=SUMPRODUCT(($E$8:$N$8="L")*(E$18:N$18="I")*($E19:$N19<>""))/$B$4

[Wikster7]

I used the second set of formulas and they worked great! Thanks for your help eibi - really appreciate it!