sumif non blank depending on criteria

[sachin483]

how can i sum column D of non blank column C depending criteria of column A i have tried sumif but it totaling full column


[TABLE="width: 196"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT[/TD]
[TD]Q1[/TD]
[TD]46228[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT[/TD]
[TD]Q2[/TD]
[TD]76700[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT[/TD]
[TD]Q3[/TD]
[TD]67967[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT[/TD]
[TD]Q4[/TD]
[TD]67967[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT[/TD]
[TD]AC[/TD]
[TD]30443[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT[/TD]
[TD]AG[/TD]
[TD]606220[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT Total[/TD]
[TD] [/TD]
[TD]895525[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]TTT Avg[/TD]
[TD] [/TD]
[TD]316068[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT[/TD]
[TD]Q1[/TD]
[TD]46228[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT[/TD]
[TD]Q2[/TD]
[TD]76700[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT[/TD]
[TD]Q3[/TD]
[TD]67967[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT[/TD]
[TD]Q4[/TD]
[TD]67967[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT[/TD]
[TD]AC[/TD]
[TD]30443[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT[/TD]
[TD]AG[/TD]
[TD]606220[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT Total[/TD]
[TD] [/TD]
[TD]895525[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]TTT Avg[/TD]
[TD] [/TD]
[TD]316068[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=SUMIF(C1:C17,"<>",D1:D17)

 

[AlanY]

try

=SUM(IF($A$2:$A$17="AA",IF(LEN($C$2:$C$17)>0,$D$2:$D$17)))

confirm with shift-control-enter together

 

[Aladin Akyurek]

=SUMIFS(D:D,A:A,"AA",C:C,"?*")

should produce the conditional sum you want.

 

[sachin483]

thanks a lot @Alan Y & @Aladin Akyurek both the formula working fine

 

[AlanY]

thanks a lot @Alan Y & @Aladin Akyurek both the formula working fine

you're welcome
I'm sure you noticed that Aladin's offer is a better/efficient option without the use of array.