Countifs or AverageIfs

[dreicer_Jarr]

I would like to use the countifs or averageifs formula where if any of the criteria values (A2, B2 or C2) are blank the result is the count or average of the other two (or one) criteria values and NOT the count or average of all three criteria values (the second/third would be looking for blanks and not just considering the other one or two criteria). So taking the example below, I want to average the "one" column if the "two" column equals the value in A2 and the "three" column equals B2 and the "four" column equals C2. BUT, say A2 is blank. In this case, I want to disregard the "two" column entirely and just do the average if B2 is found in "three" and C2 is found in "four". The same would go for if any B2 and/or C2 is blank; I would would just want to take the average if of the other one or two criteria. The attached example will be the easiest way to understand the issue and the result I am trying to get at.

=AVERAGEIFS(One,two,A2,three,B2,four,C2)

I created a workaround with IF statement dummy formulas that essentially say "if my criteria cell is blank, give me some standard value so the end formula gives me what I want but this is entirely too inefficient and my spreadsheet is now too large and slow. There must be an easier way, no?

Help!?

[Special-K]

Try
=SUM(A2:C2)/COUNTA(A2:C2)

[dreicer_Jarr]

"One", "Two", "Three" and "Four" are ranges that each represent a very long column of data. "One" has the values I want to average and the other three ranges contain the data the formula evaluates to decide whether or not to average the values in the "one" column. Your formula only addresses the input (criteria) cells, unless I am misunderstanding? I apologize for not being more clear to begin with.

Thanks,

J

[dreicer_Jarr]

Take a look at the example I attached, please!

[Teethless mama]

Try to put * in L1 instead of blank

[kvsrinivasamurthy]

Try this formula in M3.you can Keep any of the K1, L1, M1 cells blank.

Eg: If K1 is blank It gives average of all L1 & M1.

Please Login or Register  to view this content.