How to get total "conditional sum of cells" in a column?

Hi all,

I have dollar amounts in one col, and status in another. I want the
sum of those dollar amounts where the corresponding status cell is
empty (blank). How do I do this?

Thanks for any hints,

cdj
0
4/7/2008 7:54:14 PM
excel 39879 articles. 2 followers. Follow

5 Replies
713 Views

Similar Articles

[PageSpeed] 42

Status in Column A and dollar amounts in Column B:

=SUMPRODUCT((A2:A100="")*B2:B100)

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message 
news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.googlegroups.com...
> Hi all,
>
> I have dollar amounts in one col, and status in another. I want the
> sum of those dollar amounts where the corresponding status cell is
> empty (blank). How do I do this?
>
> Thanks for any hints,
>
> cdj
> 


0
sandymann2 (1054)
4/7/2008 8:23:31 PM
=SUMIF(B1:B20,"",A1:A20)


Gord Dibben  MS Excel MVP

On Mon, 7 Apr 2008 12:54:14 -0700 (PDT), sherifffruitfly
<sherifffruitfly@gmail.com> wrote:

>Hi all,
>
>I have dollar amounts in one col, and status in another. I want the
>sum of those dollar amounts where the corresponding status cell is
>empty (blank). How do I do this?
>
>Thanks for any hints,
>
>cdj

0
Gord
4/7/2008 8:25:03 PM
Use the SUMIFS function. For example, =SUMIFS(A1:A12,B1:B12,"").


"sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message 
news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.googlegroups.com...
> Hi all,
>
> I have dollar amounts in one col, and status in another. I want the
> sum of those dollar amounts where the corresponding status cell is
> empty (blank). How do I do this?
>
> Thanks for any hints,
>
> cdj 

0
test1951 (392)
4/7/2008 8:26:29 PM
This is a multi-part message in MIME format.

------=_NextPart_000_0132_01C898B4.58487D20
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi CDJ,

You want to use =3DSUMIF(StatusRange,"<>",DollarRange)

Where StatusRange is the column containing the status and DollarRange is =
the column containing the dollar amounts.

Cheers,
Shane


"sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message =
news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.googlegroups.com...
> Hi all,
>=20
> I have dollar amounts in one col, and status in another. I want the
> sum of those dollar amounts where the corresponding status cell is
> empty (blank). How do I do this?
>=20
> Thanks for any hints,
>=20
> cdj
------=_NextPart_000_0132_01C898B4.58487D20
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dunicode">
<META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR></HEAD>
<BODY id=3DMailContainerBody=20
style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"=20
bgColor=3D#ffffff leftMargin=3D0 topMargin=3D0 CanvasTabStop=3D"true"=20
name=3D"Compose message area">
<DIV><FONT face=3DArial size=3D2>Hi CDJ,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>You want to use=20
=3DSUMIF(StatusRange,"&lt;&gt;",DollarRange)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Where StatusRange is the column =
containing the=20
status and DollarRange is the column containing the dollar =
amounts.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Cheers,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Shane</FONT></DIV>
<DIV><BR><BR>"sherifffruitfly" &lt;sherifffruitfly@gmail.com&gt; wrote =
in=20
message=20
news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.googlegroups.com...<=
BR>&gt;=20
Hi all,<BR>&gt; <BR>&gt; I have dollar amounts in one col, and status in =

another. I want the<BR>&gt; sum of those dollar amounts where the =
corresponding=20
status cell is<BR>&gt; empty (blank). How do I do this?<BR>&gt; <BR>&gt; =
Thanks=20
for any hints,<BR>&gt; <BR>&gt; cdj</DIV></BODY></HTML>

------=_NextPart_000_0132_01C898B4.58487D20--

0
4/7/2008 8:36:12 PM
On Apr 7, 1:36=A0pm, "Shane Devenshire" <shanedevensh...@sbcglobal.net>
wrote:
> Hi CDJ,
>
> You want to use =3DSUMIF(StatusRange,"<>",DollarRange)
>
> Where StatusRange is the column containing the status and DollarRange is t=
he column containing the dollar amounts.
>
> Cheers,
> Shane
>
>
>
> "sherifffruitfly" <sherifffruit...@gmail.com> wrote in messagenews:bc08584=
b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.googlegroups.com...
> > Hi all,
>
> > I have dollar amounts in one col, and status in another. I want the
> > sum of those dollar amounts where the corresponding status cell is
> > empty (blank). How do I do this?
>
> > Thanks for any hints,
>
> > cdj- Hide quoted text -
>
> - Show quoted text -

Awesome - thanks everyone!
0
4/7/2008 8:58:32 PM
Reply:

Similar Artilces:

Conditional Sum of cells in a row
Hi, I'm using Excel 2002 and would like to know how to do the following: I have 7 cells in a row in which scores will be entered. I need to compare the scores and then sum the 4 highest scores to get my total. example ( each number represents 1 of the 7 cells in my row): 1 2 2 3 0 3 2, producing the result of 10 or 2 2 2 2 2 0 1, producing the result of 8, or 1 1 1 1 1 1 2, producing the result of 5, etc. I hope this makes sense. I know its probably simple, but I can't figure out how to do it. Any help would be greatly appreciated! Thanks, Ron Hi try =SUM(...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...