| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
| If's conditions about matching | Excel Worksheet Functions | |||
| sum on conditions..... | Excel Discussion (Misc queries) | |||
| IF, AND, OR conditions? | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Linear Mode
