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.

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.

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.

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.

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.

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.

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.