Average content distributed in more than one column

If the "certain text" is in column A and your values to average are in B and C and there is no chance that a row with the "certain text" will have a number in both B and C,

=(SUMIF(A,"certain text",B)+SUMIF(A,"certain text",C))/(COUNTIF(A,"certain text")

If a row with the "certain text" in A may have a value in both B and C to be included in the average,

=(SUMIF(A,"certain text",B)+SUMIF(A,"certain text",C))÷(COUNTIFS(A,"certain text",B,"<>")+COUNTIFS(A,"certain text",C,"<>"))

Blank cells can be totally blank or have a null string in them to be excluded from the average.

A zero will be included in the average

EDIT: I see Barry had the same basic idea except I think his COUNTIFS conditions for B and C to be >0 limits it to positive numbers

Here are two examples (both in the same table). Amounts to be summed or averaged are in columns B and E. the 'certain text' ( "Yea" ) determining inclusion is in columns C and F.

The Average of the selected values is displayed in cells I2 and J2.

Sample 1:

Sample 2:

The checkbox in row 16 is used to trigger recalculation of the table, including the RAND and RANDBETWEEN formulas in columns B, C, E and F setting the values in those columns.

Sample 3, and a look at the formulas calculating the two averages.

G to H are the rightmost four columns in the table.

G2: COUNTIF counts the number of 'Yea's in columns C and F. SUM returns the SUM of the two counts, which is displayed in the cell containing the formula.

H2: Each of the SUMIF formulas here sums the cells in rows of C or F where the value in column B or E is "Yea". The two results are added using the addition operator ( + ) shown between them.

I2: The formula gets the Sum from H2 and divides it by the Count from G2. The result is the required Average, and is displayed in the formula's cell, I2 (eye too).

H2: This formula uses the UNION.RANGES function to join the two columns containing counts and the two columns containing Yeas and Nays into a single pair of ranges, which is then handed to AVERAGEIF which calculates the Average of the "Yea" marked values.

Regards,

Barry

Here's a second go, using your sample data, as presented above:

The tale on the left is a recreation of your example above, with the data used in that sample table.

The formulas in Data::B11 and Data::B12 are essentially the same, as the only changes are the differences in the "condition" references in SUMIF and COUNTIFS.

Rows 11and 12 of this table are Footer Rows.

B11: (SUMIF(A,"A",B)+SUMIF(A,"A",C))÷
(COUNTIFS(A,"A",B,">"&"0")+COUNTIFS(A,"A",C,">"&"0"))

B12: (SUMIF(A,"B",B)+SUMIF(A,"B",C))÷(COUNTIFS(A,"B",B,">"&"0")+COUNTIFS(A,"B",C,">"&"0"))

The table on the right is a check on the calculations using the same values (and empty cells), sorted into separate single columns.

Formulas are AVERAGE(A) in A11 and AVERAGE(B) in B11.

As in the Data table, row 11 is a Footer row, allowing use of the letter only column reference to column A (and to column B) without incurring a "Self reference' error message.

Regards,

Barry

The "<>" condition counts all numbers including zero (which is necessary to be counted) but it is not perfect either. It should be checking that the cell is not empty (blank or the null string "") just like the condition in an IF statement such as IF(B2<>"",true, false). So it will count anything, including text, while the SUMIF in the formula sums only numbers. As long as the columns contain only numbers (or blank cells or null strings) , no problem.

"EDIT: I see Barry had the same basic idea except I think his COUNTIFS conditions for B and C to be >0 limits it to positive numbers"

True. Your use of "<>" in place of ">" should allow positive and negative values. Can that condition work without the &0 included to tell numbers what value the cell contents can't be?

Regards,

Barry