Calculate sum and average treating blank values with specific values based on other column condition-CodePudding

I would like to calculate the sum and average in Google Spreadsheet of a range based on conditions from another column, but treat blanks with a specified value. It can be accomplished using a helper column, but I would like to do it without it. Here is the sample data:

I would like to sum values in column B based on value on Column A, but replacing blanks values with the value specified on E2 and E3 respectivelly.

Here is a sample in google sheet: https://docs.google.com/spreadsheets/d/1Cv9YxFMHuGq2biNNCdGsjU8cAD_OwCPTR4YCPLc2r34/edit?usp=sharing

I was trying to use the following formulas for the sum of team A but I am not getting the expected result:

=sumif(A2:A,"A", if(B2:B<>"",B2:B,E2)) returns 6 instead of 19
=sum(if(A2:A="A",if(B2:B<>"", B2:B, E2),)) return 27 instead of 19

I cannot use a combintation of sumif and arrayformula like this because it expects a range in the third input argument:

=sumif(A2:A,"A", ARRAYFORMULA(if(B2:B<>"", B2:B,E2)))

CodePudding user response：

I wasn't going to jump in on this one, since it's after midnight and I didn't feel I had the energy to both write and explain such a formula. But I see that you yourself have helped others on this forum. So I'll soldier through for you.

Delete everything from columns G:I (i.e., leave those columns entirely blank); and I suggest removing all of the formatting that you currently have in place in those columns, since it won't make sense after what I propose below.

Place the following formula in G1:

=ArrayFormula(QUERY(FILTER({A2:A,IF(B2:B="",IFERROR(VLOOKUP(A2:A&"*",D:E,2,FALSE),0),B2:B)},A2:A<>""),"Select Col1, SUM(Col2), AVG(Col2) GROUP BY Col1 LABEL Col1 'Team', SUM(Col2) 'Sum', AVG(Col2) 'Average'"))

This one formula will generate all headers, team names and results for all teams' sums and averages.

The virtual array between the curly brackets pairs every element of A2:A with the results of the IF function. That IF function checks to see if B2:B is blank. If so, a VLOOKUP with wildcard is performed to find the Col-A team name at the start of any value in Col D and, if found, returns the corresponding filler value from Col E. (If not found, IFERROR returns 0 as the filler value. It's important to have a numerical value because of the way QUERY works.)

FILTER filters in all of the above results only for those rows where A2:A contains a non-null value.

QUERY then pulls the team names, sums and averages; the LABEL portion of the QUERY assigns your desired column headers to the results.

This formula is not restricted to only two teams. You can add as many teams as you like in A:B and assign as many filler values as necessary in D:E.

It's important to note, however, that the formula relies on the FULL team name found in A:A being found at the beginning of the Col-D values. So if your team name is "Bears," just make sure the corresponding entry in Col-D starts with "Bears" as well (e.g., "Bears blanks" or even just "Bears").

You'll need to format the entire Col H as whole numbers and the entire Col I as 0.00 to match the results you shown in your sample.

CodePudding user response：

You can use a combination of sumifs and countifs

to add up the non-blanks

=sumifs(B2:B10,A2:A10,"A")

to add up the blanks (and multiply by the default value)

=countifs(A2:A10,"A",B2:B10,"")*E2

all together

=sumifs(B2:B10,A2:A10,"A") countifs(A2:A10,"A",B2:B10,"")*E2

Average (use countifs to work out how many items):

=(sumifs(B2:B10,A2:A10,"A") countifs(A2:A10,"A",B2:B10,"")*E2)/countifs(A2:A10,"A")