This is a little confusing but I'll try my best to explain my problem.
In column E I have a list of strings, and I want to first determine if the string 00-EI is in this column. If this is true I want to then look at the next 2 columns (c and d) and determine if corresponding cells(ie. c3 & d3) both contain a value.
From there I want to display this as a percentage of corresponding non blanks in c AND d over the total cells (in column E) containing 00-EI.
When I use the following syntax it gives me a circular warning(I think). It calculates the correct value for the first scenario, but if I copy the cell and change it to check for 00-EA, it still returns the same value as the first(wrong value) and then if I change the cells the formula is referencing it doesn't change the formulas output. (Might have something to do with circular problem I think)
Here is the syntax I am trying to use:
=(IF(qryCableRoutingSchedule!E:E = '00-VI', (SUMPRODUCT(--(qryCableRoutingSchedule!C2:C351 <> ""),--(qryCableRoutingSchedule!D2:D351 <> ""))), 0))/(COUNTIF(qryCableRoutingSchedule!$E$1:$E$1000,"=00-VI"))
Thanks, sorry if it's not clear
In column E I have a list of strings, and I want to first determine if the string 00-EI is in this column. If this is true I want to then look at the next 2 columns (c and d) and determine if corresponding cells(ie. c3 & d3) both contain a value.
From there I want to display this as a percentage of corresponding non blanks in c AND d over the total cells (in column E) containing 00-EI.
When I use the following syntax it gives me a circular warning(I think). It calculates the correct value for the first scenario, but if I copy the cell and change it to check for 00-EA, it still returns the same value as the first(wrong value) and then if I change the cells the formula is referencing it doesn't change the formulas output. (Might have something to do with circular problem I think)
Here is the syntax I am trying to use:
=(IF(qryCableRoutingSchedule!E:E = '00-VI', (SUMPRODUCT(--(qryCableRoutingSchedule!C2:C351 <> ""),--(qryCableRoutingSchedule!D2:D351 <> ""))), 0))/(COUNTIF(qryCableRoutingSchedule!$E$1:$E$1000,"=00-VI"))
Thanks, sorry if it's not clear
