Sum of sum criteria

[gobblechops]

Hi all,

Sure this is simple for an expert.

Below is the formula to return the value of A3 if the values in row C add to zero.

=(IF(SUM(C3:F3)=0,A3,0))

How do I sum the total of all of all the values from A3:A15 that meet the criteria using relative cell references for column C criteria?

Thanks!

 

[gobblechops]

Just to clarify, I can get the answer with:

=SUMIFS(A3:A15,C3:C15,0,D3:D15,0,E3:E15,0,F3:F15,0)

But is there a more elegant formula, for example if there were 50 criteria columns rather than 4?

Thanks.

 

[Fluff]

You could put
=SUM(C3:F3)
in another column, drag down & then use
=SUMIF(G3:G15,0,A3:A15)

 

[Besserwisser]

Hi,

without Helper-Column

=SUMPRODUCT(A3:A15,--(MMULT(--(C3:F15=0),TRANSPOSE(COLUMN(C:F)^0))=COLUMNS(C:F)))



Greetings



Christian

 

[HJimHB]

Is there a way to sum alternate columns for a large range? I have a report that goes to column LS with data from alternate sources and I need to total each source for reconciliation. Unfortunately it wouldn't be practical to re-order the columns in the way suggested above.

Manually going through each column would be impractical and I've not been able to work out how to customise the =mod(column(),2) function suggested above.

Thanks