Using multiple conditions for a formula

[guilbj2]

I'm using a formula to calculate average call length for some staff an
want to "bullet proof" it a little. I originally had 4 items t
calculate and used counta to verify if all 4 fields were filled i
before completing the calculation. What I need to do now is accoun
for 3 seperate types of calls. I've modified the formula to calculat
the info properly, but I'm having troubles with the "if" statement. I
order to run the calculations for the 3 different queues, 4 variable
must be filled in. The first queue's data is entered into C4, D4, E4
F4 the second is C5... and the third is C6...

What I want to do is complete the calculations if any or all of th
groups have all 4 data fields filled in with amounts greater than 0.
was using COUNTA to simply verify if all 4 fields were filled in before
but with the additional 2 data sets added, I'm lost. I've included th
formula I'm using. Currently, the COUNTA verifies if all 12 fields ar
filled in but this isn't suffiencient since some people won't have dat
for all 12. If anyone has any ideas, they'd be greatly appreciated..
I'm at a loss on this one...

=IF(COUNTA(C4:C6,D46,E4:E6,F4:F6)=12,((SUMPRODUCT(C4:C6*E4:E6))+(SUMPRODUCT(D46*F4:F6))),
")

P.S. I know that the calculation isn't figuring out the average yet.
I can fix that on my own, but want to figure out the IF statemen
first

 

[Frank Kabel]

Hi
use COUNTIF. e.g.
=IF(COUNTIF(C4:F4,">0")=4,your_formula,"")

 

[guilbj2]

hi Frank,

Thanks for the reply. The countif will definitely take care of th
values that are 0, but I'd like the calculation to be completed if 4,
or 12 of the data cells have been filled in. Is there any way to ge
the formula to work like If A1=4 OR 8 OR 12, "formula here"

By the way, where did you learn all of this ? You answer ALL the toug
ones...lol

 

[Frank Kabel]

Hi
if you don't care in which rows these values are try:
=IF(MOD(COUNTIF(C4:F4,">0"),4)=0,your_formula,"")

 

[guilbj2]

I'm sure I'm driving you nuts here, but it does matter which row the
values are for. What I need to do is process the formula if all 4 data
fields in 1 or more of the 3 rows are present and greater than 0. So if
all four entries (c4,d4,e4,f4) are greater than 0, the formula would
run. Likewise, if only row 5 or row 6 (or any combination like 4 and 5
but not 6) had all 4 data fields >0, the formula would run. Sorry if
I'm not making sense here. I really do appreciate your help.

 

[Frank Kabel]

Hi
just as I have expected

Try:
IF(SUMPRODUCT(--(COUNTIF(OFFSET(C4:F4,ROW(INDIRECT("1:3"))-1,0),">0")=4
))>0,your_formula,"")

 

[guilbj2]

Thanks very much Frank, this worked like a charm