2 Conditions + Sum of a colum matching those conditions
 

Ok.. I've spent the last 2 hours reading this area trying to find a close
enough match to what I am looking for. I know this will be simple.. but I've
ended up having to post.. I'm sorry :(.

This is what I have..

Col c = windows desktop or windows laptop
Col g = It's monthly $ charge
Col o = Who it is owned by (Finance companies)

I would like to have a breakdown of Desktops laptops with a cost item.. For
example

If col c = "Windows Desktop" & Col o = "HP Finance" Sum(g:g) (what's left
after the 2 conditions are met.

Hope that makes sense.. I'd love some help :)
Regards,

Jeffrey

2 Conditions + Sum of a colum matching those conditions
 

=SUMPRODUCT(--(C2:C200="Windows Desktop"),--(O2:O200="HP Finance"),G2:G200)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jeffa" wrote in message
...
Ok.. I've spent the last 2 hours reading this area trying to find a close
enough match to what I am looking for. I know this will be simple.. but
I've
ended up having to post.. I'm sorry :(.

This is what I have..

Col c = windows desktop or windows laptop
Col g = It's monthly $ charge
Col o = Who it is owned by (Finance companies)

I would like to have a breakdown of Desktops laptops with a cost item..
For
example

If col c = "Windows Desktop" & Col o = "HP Finance" Sum(g:g) (what's left
after the 2 conditions are met.

Hope that makes sense.. I'd love some help :)
Regards,

Jeffrey

2 Conditions + Sum of a colum matching those conditions
 

You can use an array* formula like this:

=SUM(IF((C1:C100="Windows desktop")*(O1:O100="HP Finance"),G1:G100,0))

* As this is an array formula, then when you type it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER to commit it
rather than just ENTER. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

An alternative (non-array) formula is:

=SUMPRODUCT(--(C1:C100="Windows desktop"),--(O1:O100="HP
Finance"),G1:G100)

In both cases the array ranges should be the same size, but cannot be
a complete column - I've assumed you have 100 of them, so adjust this
to suit your data.

Hope this helps.

Pete

On Jun 7, 5:27 am, Jeffa wrote:
Ok.. I've spent the last 2 hours reading this area trying to find a close
enough match to what I am looking for. I know this will be simple.. but I've
ended up having to post.. I'm sorry :(.

This is what I have..

Col c = windows desktop or windows laptop
Col g = It's monthly $ charge
Col o = Who it is owned by (Finance companies)

I would like to have a breakdown of Desktops laptops with a cost item.. For
example

If col c = "Windows Desktop" & Col o = "HP Finance" Sum(g:g) (what's left
after the 2 conditions are met.

Hope that makes sense.. I'd love some help :)
Regards,

Jeffrey

2 Conditions + Sum of a colum matching those conditions
 

Pete,

You don't need the ,0 at the end of this formula, FALSE doesn't sum. Saves a
bit of typing <G

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Pete_UK" wrote in message
ups.com...
You can use an array* formula like this:

=SUM(IF((C1:C100="Windows desktop")*(O1:O100="HP Finance"),G1:G100,0))

* As this is an array formula, then when you type it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER to commit it
rather than just ENTER. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

An alternative (non-array) formula is:

=SUMPRODUCT(--(C1:C100="Windows desktop"),--(O1:O100="HP
Finance"),G1:G100)

In both cases the array ranges should be the same size, but cannot be
a complete column - I've assumed you have 100 of them, so adjust this
to suit your data.

Hope this helps.

Pete

On Jun 7, 5:27 am, Jeffa wrote:
Ok.. I've spent the last 2 hours reading this area trying to find a close
enough match to what I am looking for. I know this will be simple.. but
I've
ended up having to post.. I'm sorry :(.

This is what I have..

Col c = windows desktop or windows laptop
Col g = It's monthly $ charge
Col o = Who it is owned by (Finance companies)

I would like to have a breakdown of Desktops laptops with a cost item..
For
example

If col c = "Windows Desktop" & Col o = "HP Finance" Sum(g:g) (what's left
after the 2 conditions are met.

Hope that makes sense.. I'd love some help :)
Regards,

Jeffrey

2 Conditions + Sum of a colum matching those conditions
 

Hi Bob,

yes, I know, and you have pointed it out to me in the past - I keep
doing it, it's ingrained in my psyche !! <bg

Pete

On Jun 7, 10:16 am, "Bob Phillips" wrote:
Pete,

You don't need the ,0 at the end of this formula, FALSE doesn't sum. Saves a
bit of typing <G

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Pete_UK" wrote in message

ups.com...



You can use an array* formula like this:

=SUM(IF((C1:C100="Windows desktop")*(O1:O100="HP Finance"),G1:G100,0))

* As this is an array formula, then when you type it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER to commit it
rather than just ENTER. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

An alternative (non-array) formula is:

=SUMPRODUCT(--(C1:C100="Windows desktop"),--(O1:O100="HP
Finance"),G1:G100)

In both cases the array ranges should be the same size, but cannot be
a complete column - I've assumed you have 100 of them, so adjust this
to suit your data.

Hope this helps.

Pete

On Jun 7, 5:27 am, Jeffa wrote:
Ok.. I've spent the last 2 hours reading this area trying to find a close
enough match to what I am looking for. I know this will be simple.. but
I've
ended up having to post.. I'm sorry :(.

This is what I have..

Col c = windows desktop or windows laptop
Col g = It's monthly $ charge
Col o = Who it is owned by (Finance companies)

I would like to have a breakdown of Desktops laptops with a cost item..
For
example

If col c = "Windows Desktop" & Col o = "HP Finance" Sum(g:g) (what's left
after the 2 conditions are met.

Hope that makes sense.. I'd love some help :)
Regards,

Jeffrey- Hide quoted text -

- Show quoted text -

2 Conditions + Sum of a colum matching those conditions
 

Bob,

Thank you so much for your help.. You are a stress saver!! :o)

"Bob Phillips" wrote:

=SUMPRODUCT(--(C2:C200="Windows Desktop"),--(O2:O200="HP Finance"),G2:G200)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jeffa" wrote in message
...
Ok.. I've spent the last 2 hours reading this area trying to find a close
enough match to what I am looking for. I know this will be simple.. but
I've
ended up having to post.. I'm sorry :(.

This is what I have..

Col c = windows desktop or windows laptop
Col g = It's monthly $ charge
Col o = Who it is owned by (Finance companies)

I would like to have a breakdown of Desktops laptops with a cost item..
For
example

If col c = "Windows Desktop" & Col o = "HP Finance" Sum(g:g) (what's left
after the 2 conditions are met.

Hope that makes sense.. I'd love some help :)
Regards,

Jeffrey