Summing up horizontally with condition

问题:

I have a data represented as below:

ENO    A    B   C   D   
1001    0   0   1.2 0   
1001    1.9 0   0   2.2 
1002    0   0   0   0   
1002    0   0   6.6 0   
1002    0   0   3   0   
1003    0   0   0   0   
1003    0   0   0   1   
1004    0   2   0   0   
1004    0   3   0   0   
1004    0   0   0   0   
1004    0   0   2   0   

I'm trying to sum up columns A, B, C and D horizontally like below:

ENO     A   B   C   D           ENO       SUM_T
1001    0   0   1.2 0          1001        5.3
1001    1.9 0   0   2.2        
1002    0   0   0   0          1002        9.6
1002    0   0   6.6 0   
1002    0   0   3   0   
1003    0   0   0   0          1003        1
1003    0   0   0   1   
1004    0   2   0   0          1004        7
1004    0   3   0   0   
1004    0   0   0   0   
1004    0   0   2   0

How do I specify the formula?

回答1:

Given:

In F1 enter:

=SUM(B1:E1)

and copy down. In G1 enter:

=A1

In G2 enter:

=IF(A2=A1,"",A2)

and copy down. In H1 enter:

=SUMIF(A:A,G1,F:F)

In H2 enter:

=IF(G2="","",SUMIF(A:A,G2,F:F))

and copy down:

回答2:

You can use a SUMPRODUCT function to supply a SUMIF for multiple columns.

    

The formulas in F2:G2 are:

=IF(A2<>A1, A2, "")
=IF(F2="", "", SUMPRODUCT(B$2:INDEX(E:E, MATCH(1E+99,A:A ))*(A$2:INDEX(A:A, MATCH(1E+99,A:A ))=F2)))

Fill down as necessary.

The SUMPRODUCT function does not like full column column references, preferring to calculate every row whether there is anything populating the cell or not. This adaptation restricts the scope of the ranges to only exactly what is required.

回答3:

There may be a more eloquent way to accomplish this, but pictured here is how I would do it. This uses four SUMIF functions, one for each column, added together to calculate the total. For the first 'ENO' value 1001, the function looks in the first column (marked 'A') and sums any match to the value 1001. Then we do this for the second column (marked 'B') and so forth. This is repeated for each value that you want a sum total for.

Formula used for the first 'ENO' value 1001:

=SUMIF(A:A,F2,B:B)+SUMIF(A:A,F2,C:C)+SUMIF(A:A,F2,D:D)+SUMIF(A:A,F2,E:E)

This formula would be repeated for each 'ENO' value, where cell 'F2' contains the search value.

   

Please let me know if I need to elaborate on the answer or how the formulas were created. :)

Link to image

Note: Thank you Jeeped for inserting the image