Quote:
Originally Posted by Archi G
Hi,
I need help for a Excel formula -
I want cells C19, C20, C21 to sum the values of the categories in column D, if the category cell(column D) is empty I would like the number to 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 is blank :
=SUMIF(D4:D16,"SP",C4:D16)
|
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 use
=SUMPRODUCT(--($D$2:$D$14="SP")+($D$2:$D$14=""),$C$2:$C$14) in C19 instead.