Sum of values based on different criteria
 

I have a column (lets say column A) that has a dropped down list of
'terminated' or 'completed' (let say from row 1 to row 5), and I have another
column (Column B) that I enter a code of 'HB' for home buying (there are
blank cells); then my last column (column C) has an amount. Now, I would
like to know how much is the total (sum) for the terminated and the total for
completed.

Any help will be appreciated.

Thanks

Sum of values based on different criteria
 

Try this:

E1 = terminated
F1 = HB

=SUMPRODUCT(--(A1:A5=E1),--(B1:B5=F1),C1:C5)

--
Biff
Microsoft Excel MVP


"Irene" wrote in message
...
I have a column (lets say column A) that has a dropped down list of
'terminated' or 'completed' (let say from row 1 to row 5), and I have
another
column (Column B) that I enter a code of 'HB' for home buying (there are
blank cells); then my last column (column C) has an amount. Now, I would
like to know how much is the total (sum) for the terminated and the total
for
completed.

Any help will be appreciated.

Thanks

Sum of values based on different criteria
 

Maybe this:

=SUMPRODUCT(--(A1:A5="terminated"),--(B1:B5="HB"),C1:C5)

HTH,
Paul

--

"Irene" wrote in message
...
I have a column (lets say column A) that has a dropped down list of
'terminated' or 'completed' (let say from row 1 to row 5), and I have
another
column (Column B) that I enter a code of 'HB' for home buying (there are
blank cells); then my last column (column C) has an amount. Now, I would
like to know how much is the total (sum) for the terminated and the total
for
completed.

Any help will be appreciated.

Thanks

Sum of values based on different criteria
 

Thanks much, it helped. One more question, what about if I would like to
find out how many in column C is zero; how many terminated are zero and how
many who doesnt have a terminated or completed (blank in column A) has zero
amount in column C?



"T. Valko" wrote:

Try this:

E1 = terminated
F1 = HB

=SUMPRODUCT(--(A1:A5=E1),--(B1:B5=F1),C1:C5)

--
Biff
Microsoft Excel MVP


"Irene" wrote in message
...
I have a column (lets say column A) that has a dropped down list of
'terminated' or 'completed' (let say from row 1 to row 5), and I have
another
column (Column B) that I enter a code of 'HB' for home buying (there are
blank cells); then my last column (column C) has an amount. Now, I would
like to know how much is the total (sum) for the terminated and the total
for
completed.

Any help will be appreciated.

Thanks

Sum of values based on different criteria
 

One more question

how many in column C is zero
=COUNTIF(C1:C5,0)

how many terminated are zero
=SUMPRODUCT(--(A1:A5=E1),--(ISNUMBER(C1:C5)),--(C1:C5=0)

how many who doesnt have a terminated
or completed (blank in column A) has zero
=SUMPRODUCT(--(A1:A5=""),--(ISNUMBER(C1:C5)),--(C1:C5=0)


--
Biff
Microsoft Excel MVP


"Irene" wrote in message
...
Thanks much, it helped. One more question, what about if I would like to
find out how many in column C is zero; how many terminated are zero and
how
many who doesnt have a terminated or completed (blank in column A) has
zero
amount in column C?



"T. Valko" wrote:

Try this:

E1 = terminated
F1 = HB

=SUMPRODUCT(--(A1:A5=E1),--(B1:B5=F1),C1:C5)

--
Biff
Microsoft Excel MVP


"Irene" wrote in message
...
I have a column (lets say column A) that has a dropped down list of
'terminated' or 'completed' (let say from row 1 to row 5), and I have
another
column (Column B) that I enter a code of 'HB' for home buying (there
are
blank cells); then my last column (column C) has an amount. Now, I
would
like to know how much is the total (sum) for the terminated and the
total
for
completed.

Any help will be appreciated.

Thanks