Need help with Excel Formula for calculating sum of products depending on criteria

[Archi G]

Hi,

I need help for a Excel formula -

I want cells C19, C20, C21 to sum the values of the categories in colum
D, if the category cell(column D) is empty I would like the number t
summed up as category SP.
"C Column" "D Column"
USD
March Category
10 SP
30 TP
20 SP
10
5 SP
15 TP
5 DI
7 DI
5
5
8 SP
10 TP
15 DI
145

SUM SP
SUM TP
SUM DI

I am using below formula but do not know how to check if cell value i
blank :
=SUMIF(D416,"SP",C416

 

[Claus Busch]

Hi Archi,

Am Mon, 12 Mar 2012 09:55:31 +0000 schrieb Archi G:

I am using below formula but do not know how to check if cell value is
blank :
=SUMIF(D416,"SP",C416)

D21: =SUMIF(D416,"DI",C4:C16)
D20: =SUMIF(D416,"TP",C4:C16)
D19: =SUM(C4:C16)-D20-D21


Regards
Claus Busch

 

[Claus Busch]

Hi Archie,

Am Mon, 12 Mar 2012 15:26:24 +0100 schrieb Claus Busch:

D21: =SUMIF(D416,"DI",C4:C16)
D20: =SUMIF(D416,"TP",C4:C16)
D19: =SUM(C4:C16)-D20-D21

or you use for SP:
=SUMPRODUCT(((D416="SP")+(D416=""))*C4:C16)


Regards
Claus Busch

 

[Spencer101]

Hi,

I need help for a Excel formula -

I want cells C19, C20, C21 to sum the values of the categories in colum
D, if the category cell(column D) is empty I would like the number t
summed up as category SP.
"C Column" "D Column"
USD
March Category
10 SP
30 TP
20 SP
10
5 SP
15 TP
5 DI
7 DI
5
5
8 SP
10 TP
15 DI
145

SUM SP
SUM TP
SUM DI

I am using below formula but do not know how to check if cell value i
blank :
=SUMIF(D416,"SP",C416)

Enter these formulas:

In C19
*=SUMPRODUCT(--($D$2:$D$14="SP"),$C$2:$C$14)+SUMPRODUCT(--($D$2:$D$14=""),$C$2:$C$14)*

In C20 - *=SUMPRODUCT(--($D$2:$D$14="TP"),$C$2:$C$14)*

In C21 - *=SUMPRODUCT(--($D$2:$D$14="DI"),$C$2:$C$14)*

There are other ways, but this is simple and does the job.

EDIT: You could us
*=SUMPRODUCT(--($D$2:$D$14="SP")+($D$2:$D$14=""),$C$2:$C$14)* in C1
instead