Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - SUM only cells in a columnar range when a corresponding cell in a row is blank 2

Status
Not open for further replies.

PSchubert

Technical User
Jun 6, 2006
50
AU
Hi Tech,

I've been pulling my hair out over this one.

The range F3:F7 holds numbers. Cell G9 holds the sum of F3:F7. I want G9 to sum only the values in that range where the corresponding cells in columns I, J and K are blank.
In other words, if F3=9, and either I3, J3 or K3 are not blank, then G9 does not include F3 in its sum.

F3=9 | I3=<> | J3=<> | K3=TRUE
F4=8 | I4=<> | J4=<> | K4=<>
F5=7 | I5=<> | J5=<> | K5=<>
F6=4 | I6=<> | J6=<> | K6=<>
F7=3 | I7=<> | J7=<> | K7=TRUE

G9=19 (or G9=F4+F5+F6)

I haven't been able to make nested IFs, SUMs or SUMIFs work for me. Obviously, I'm not going about it in the right way. I thank you most ardently for your kind attention.

 
Try this formula:

=SUMIFS(F3:F7,I3:I7,"",J3:J7,"",K3:K7,"")

When tested with your data it worked correctly.

 
hi,
[tt]
=SUMPRODUCT(ISBLANK((I3:I7))*ISBLANK((J3:J7))*ISBLANK((K3:K7))*(F3:F7))
[/tt]
or
[tt]
=SUMPRODUCT((I3:I7=0)*(J3:J7=0)*(K3:K7=0)*(F3:F7))
[/tt]
Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Thank you, JVF and Skip! All of your solutions work perfectly. I went with SUMPRODUCT, as it works across all Excel versions, including legacy.

Your help is awesome! Thanks again.
 
Glad you got what you needed.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top