CountIF and Sum in one formula?

S

Scott A

Hello,

I am trying to sun up all of the costs in one column if it was done by a
specific person.

Example:

A B
Prsn Contracts
1 JG $8000.00
2 JG $1200.00
3 SA $10000.00
4 TS $6000.00
5 TS $4500.00

What I am looking for is a formula that will tell me how much is the total
value of all of the contracts sold by JG.

Can anyone help?
 
S

Shane Devenshire

Hi,

Put JG in a cell, say H1 then use the formula

=SUMIF(A$2:A$6,H1,B$2:B$6)

This way you can copy the formula down so if H2 has "SA" then the second
formula sum his/her numbers and so on.
 
A

Ashish Mathur

Hi,

You can also create a simple pivot table with "Prsn" in the row filed area
and "Contracts" in the Data area

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
S

Scott A

I have another part that I totally forgot about. I have a second formula that
is needed and it includes another variable. I need it to narrow the sum total
to only projects of a certain type of contract by the same person.

example:

A B C
Prsn Type Contracts
1 JG MP $8000.00
2 JG DV $1200.00
3 JG MP $10000.00
4 SA MP $6000.00
5 SA DV $4500.00

I need it to calculate the total contract amount for JG and only the MP
types of contracts. Is that possible without using a pivot table?
 
S

Scott A

I have another part that I totally forgot about. I have a second formula that
is needed and it includes another variable. I need it to narrow the sum total
to only projects of a certain type of contract by the same person.

example:

A B C
Prsn Type Contracts
1 JG MP $8000.00
2 JG DV $1200.00
3 JG MP $10000.00
4 SA MP $6000.00
5 SA DV $4500.00

I need it to calculate the total contract amount for JG and only the MP
types of contracts. Is that possible without using a pivot table?
 
S

Scott A

Thank you Ashish. My problem is that i can't use a pivot table because this
spreadsheet will be stored on Google Docs and pivot tables do not work.
Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SumIF with condition and exception 3
Conditional Arrays 1
vlookup based on a different cell 3
dragging formula is giving me undesired result in cell calls. 0
Need help with using Month as criteria for SUM(COUNTIFS(... 3
Looking formula that SUM values with specific value in neighboring colum 2
Mutliple Function Query - probably using Countif 2
Conflicting Calculations 1

Top