| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria based sum question
I have data in the following format
Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria based sum question
All: =SUMIF(C:C,"A",B:B)
Regulars: =SUMPRODUCT(--(B2:B100="A"),--(A2:A100="Regular"),C2:C100) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "masik" wrote in message ... I have data in the following format Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria based sum question
Total resources for Project A
=sumproduct(--(C1:C100="A"),--(B1:B100)) Regular resources for Project A =Sumproduct(--(A1:A100="Regular"),--((--(C1:C100="A"),--(B1:B100)) HTH "masik" wrote: I have data in the following format Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria based sum question
Thanks HTH and Bob. I tried the SUMIF and it works. But not sure I understand
sumproduct. I am new to excel here so please bear with me. What is -- in the formula? Excel does not take this. I tried using copy / paste. Can you please elaborate? Thanks. "Toppers" wrote: Total resources for Project A =sumproduct(--(C1:C100="A"),--(B1:B100)) Regular resources for Project A =Sumproduct(--(A1:A100="Regular"),--((--(C1:C100="A"),--(B1:B100)) HTH "masik" wrote: I have data in the following format Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria based sum question
Thanks HTH and Bob. I tried the SUMIF and it works. But not sure I understand
sumproduct. I am new to excel here so please bear with me. What is -- in the formula? Excel does not take this. I tried using copy / paste. Can you please elaborate? Thanks. "Bob Phillips" wrote: All: =SUMIF(C:C,"A",B:B) Regulars: =SUMPRODUCT(--(B2:B100="A"),--(A2:A100="Regular"),C2:C100) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "masik" wrote in message ... I have data in the following format Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria based sum question
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "masik" wrote in message ... Thanks HTH and Bob. I tried the SUMIF and it works. But not sure I understand sumproduct. I am new to excel here so please bear with me. What is -- in the formula? Excel does not take this. I tried using copy / paste. Can you please elaborate? Thanks. "Bob Phillips" wrote: All: =SUMIF(C:C,"A",B:B) Regulars: =SUMPRODUCT(--(B2:B100="A"),--(A2:A100="Regular"),C2:C100) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "masik" wrote in message ... I have data in the following format Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria based sum question
Sorry ...there was a typo.
See Bob Phillips' reply for explanation of SUMPRODUCT. =Sumproduct(--(A1:A100="Regular"),--(C1:C100="A"),--(B1:B100)) "masik" wrote: Thanks HTH and Bob. I tried the SUMIF and it works. But not sure I understand sumproduct. I am new to excel here so please bear with me. What is -- in the formula? Excel does not take this. I tried using copy / paste. Can you please elaborate? Thanks. "Toppers" wrote: Total resources for Project A =sumproduct(--(C1:C100="A"),--(B1:B100)) Regular resources for Project A =Sumproduct(--(A1:A100="Regular"),--((--(C1:C100="A"),--(B1:B100)) HTH "masik" wrote: I have data in the following format Resource Type Resource Project Regular 10 A Regular 20 A Contractor 05 A Regular 10 B Contractor 15 B How would I find the sum of all resources (regular and contractor) for project A, B, C etc. Which formula do I use? And then sum of regulars for project A, B, C etc. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple rows of data based on criteria | Excel Worksheet Functions | |||
| Delete rows based on criteria | Excel Discussion (Misc queries) | |||
| how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
| Locate a Cell based upon criteria | Excel Discussion (Misc queries) | |||
| counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Linear Mode
