Complicated Counting problem

[jdg347]

Hi, I want to be able to first check and see which cells within column E contain the string 00-EI. Once I have determined this I would like to look at columns C & D and then count the number of corresponding pairs (ie. c3 & d3) that are non blank AND also correspond to the 00-EI in column E.

I can count the non blanks, and can determine which cells in column E contain 00-EI but when I try to combine them I cannot get the correct syntax. Here is the syntax I am trying:

=IF(qryCableRoutingSchedule!E:E="00-VI",SUMPRODUCT(--(qryCableRoutingSchedule!C2:C351 <> ""),--(qryCableRoutingSchedule!D2:D351 <> "")),0)

Here is an example of what I have

row#1 C_________D__________E
row#2 2------------6----------00-EI
row#3 1-----------___---------00-EI
row#4 3 -----------3----------00-FF
row#5 55-----------44---------00-EI

So in this case I would like the count to be 2( Row#2 and row#5) since they both correspond to 00-EI(column E) and both have values in Columns C and D.

Thanks for your help!

 

[Jonmo1]

Try

=SUMPRODUCT(--(qryCableRoutingSchedule!E2:E351="00-VI"),--(qryCableRoutingSchedule!C2:C351 <> ""),--(qryCableRoutingSchedule!D2:D351 <> ""))

 

[jdg347]

Haha Thanks! I was going about that in a terrible way I guess. Cheers!

 

[jdg347]

What if I wanted to instead of defining "00-EI", just reference the cell? How could I do that?

 

[Jonmo1]

Try this with the text string 00-E1 in cell A1

=SUMPRODUCT(--(qryCableRoutingSchedule!E2:E351=A1),--(qryCableRoutingSchedule!C2:C351 <> ""),--(qryCableRoutingSchedule!D2:D351 <> ""))

 

[jdg347]

Thanks again, got it!

One more problem though, I have everything I want working except now in my countif (same type of scenario) I want to implement the =A1 insteat of typing "=00-EI". Is this possible with countif?

Here is the syntax I am using.

COUNTIF(qryCableRoutingSchedule!$E$1:$E$1000,"=00-EI")

*This is part of a larger formula, not sure if you want to see the whole thing but here it is(Cell D17 = 00-VI):

=(SUMPRODUCT(--(qryCableRoutingSchedule!$E$2:$E$351=D17),--(qryCableRoutingSchedule!$C$2:$C$351 <> ""),--(qryCableRoutingSchedule!$D$2:$D$351 <> "")))/(COUNTIF(qryCableRoutingSchedule!$E$1:$E$1000,"=00-VI"))