About Us

Spencer101 · 08:41 PM

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SUMPRODUCT Formula Not Calculating with 2 criteria	DogLover	Excel Programming	2	November 22nd 09 11:56 PM
calculating values depending on columns in different wkbooks.	Lindy	Excel Programming	2	October 23rd 08 09:30 AM
Calculating Daily Averages for Different Products	[email protected]	Excel Programming	3	August 1st 06 08:27 PM
calculating different percentages depending on amount	pgruening	Excel Discussion (Misc queries)	6	October 24th 05 05:57 PM
Calculating totals depending on cell value	Gary Paris	Excel Programming	5	December 24th 04 10:34 PM

Menu

About Us