Problem with Sumifs and Multiple Criteria

R

redgrace

New Member
Joined
Jan 28, 2012
Messages
5
Hello, I currently have a workbook where I have two tabs. The second tab is a copy/paste from an export where the columns will remain the same though the length may differ.

I have the following formula that works great for getting the hours an employee worked on a certain project.

=SUMIF('ADP Hours'!F2:F557, A2, 'ADP Hours'!I2:I555)

but now I need to add those hours up based off of Opportunity AND Activity.
I will then have to add up the Units Completed based off of the Opportunity AND Activity

Here are the columns with some sample data
Worked Opportunity Worked Activity Worked Units Completed Hours
CSIR. Document Preparation 2 4
CSIR. Document Preparation 3.5
Kings Document QA 2 3.25
Kings Document Preparation 2.5
MOC Document Index 2 2.25
MOC Document Preparation 3.25
RMC Document Preparation 2 1.25
RMC Document Preparation 6.25

So, for example, I would want that 5.5 hours were completed in MOC and 3.5 hours were in QA and 2.5 were Prep and 2 units were done in index and 0 units were done in prep.

I used SUMIFS but that did not yield me any answer.
=SUMIFS('ADP Hours'!I2:I564, 'ADP Hours'!F2:F559, totals!A2, 'ADP Hours'!G2:G558, totals!E1)

ADP Hours tab that I am using for the pasted information.


Any help would be appreciated.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
redgrace said:
Hello, I currently have a workbook where I have two tabs. The second tab is a copy/paste from an export where the columns will remain the same though the length may differ.

I have the following formula that works great for getting the hours an employee worked on a certain project.

=SUMIF('ADP Hours'!F2:F557, A2, 'ADP Hours'!I2:I555)

but now I need to add those hours up based off of Opportunity AND Activity.
I will then have to add up the Units Completed based off of the Opportunity AND Activity

Here are the columns with some sample data
Worked Opportunity Worked Activity Worked Units Completed Hours
CSIR. Document Preparation 2 4
CSIR. Document Preparation 3.5
Kings Document QA 2 3.25
Kings Document Preparation 2.5
MOC Document Index 2 2.25
MOC Document Preparation 3.25
RMC Document Preparation 2 1.25
RMC Document Preparation 6.25

So, for example, I would want that 5.5 hours were completed in MOC and 3.5 hours were in QA and 2.5 were Prep and 2 units were done in index and 0 units were done in prep.

I used SUMIFS but that did not yield me any answer.
=SUMIFS('ADP Hours'!I2:I564, 'ADP Hours'!F2:F559, totals!A2, 'ADP Hours'!G2:G558, totals!E1)

ADP Hours tab that I am using for the pasted information.


Any help would be appreciated.
Click to expand...
The formula references unequally-sized ranges, which might explain the unexpected result...

On the totals sheet:

=SUMIFS('ADP Hours'!I2:I564, 'ADP Hours'!F2:F1264, A2, 'ADP Hours'!G2:G1264, E1)
 
Upvote 0
HOLY COW YOU MADE IT WORK!!!!!

What kinda magician are you? Can I sit at your feet you can be my yoda?

Thanks so much!
 
Upvote 0
=SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$6,'(1)'!$A$5:$A$20905,$A5,'(1)'!$I$5:$I$20905,$D5) Where a5:a20905 is date range ,A5 is date, i5:i20905 is name range,i5 is name, o5:aa20905 is name range of artcle used , N5:N20905 is amout of article used.it returns #VALUE! ,although there is value in corresponding cells.
 
Upvote 0
dockhem said:
=SUMIFS(
'(1)'!$O$5:$AA$20905,
'(1)'!$N$5:$Z$20905,MEDICIN!$G$6,
'(1)'!$A$5:$A$20905,$A5,
'(1)'!$I$5:$I$20905,$D5)

Where a5:a20905 is date range ,A5 is date, i5:i20905 is name range,i5 is name, o5:aa20905 is name range of artcle used , N5:N20905 is amout of article used.it returns #VALUE! ,although there is value in corresponding cells.
Click to expand...

What range must be summed exactly from within O5:AA20905?
 
Upvote 0
data in O5:AA20905 on the basis of criteria in N5:Z20905. Sorry for wrong typing in question it is N5:Z20905 in name of article and O5:AA20905 is amount of article.
 
Upvote 0
dockhem said:
data in O5:AA20905 on the basis of criteria in N5:Z20905. Sorry for wrong typing in question it is N5:Z20905 in name of article and O5:AA20905 is amount of article.
Click to expand...

Are we summing O5:O20905 or P5:P2065, or ...?

Summing is based based on what? N5:N20905, O5:O20905, P5:P20905, etc.?

You ranges overlap and they don't have the same dimension. Hence #VALUE!...
 
Upvote 0

<tbody>
[TD="width: 16"][/TD]
[TD="width: 16"][/TD]
[TD="width: 16"][/TD]
[TD="width: 16"][/TD]
[TD="width: 16"][/TD]
[TD="width: 16"][/TD]
[TD="width: 16"][/TD]
[TD="width: 56"][/TD]
[TD="width: 18"][/TD]
[TD="width: 18"][/TD]
[TD="width: 18"][/TD]
[TD="width: 18"][/TD]
[TD="width: 46"][/TD]
[TD="width: 29"][/TD]
[TD="width: 20"][/TD]
[TD="width: 47"][/TD]
[TD="width: 34"][/TD]
[TD="width: 22"][/TD]
[TD="width: 45"][/TD]
[TD="width: 34"][/TD]
[TD="width: 20"][/TD]
[TD="width: 46"][/TD]
[TD="width: 31"][/TD]
[TD="width: 18"][/TD]
[TD="width: 45"][/TD]
[TD="width: 28"][/TD]

[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68"][/TD]

[TD="class: xl69, align: right"]03-04-14[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: left"]ramesh[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: left"]pencil[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: left"]book[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: left"]book[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: left"]book[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: left"]book[/TD]
[TD="class: xl70, align: right"]2[/TD]

[TD="class: xl71, align: right"]01-04-14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]dinesh[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pen[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl77, align: left"]book[/TD]
[TD="class: xl76, align: right"]3[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl72, align: right"]3[/TD]

[TD="class: xl71, align: right"]02-04-14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]dinesh[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl78, align: left"]book[/TD]
[TD="class: xl78, align: right"]4[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78, align: left"]book[/TD]
[TD="class: xl78, align: right"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pencil[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl78, align: left"]book[/TD]
[TD="class: xl78, align: right"]4[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78, align: left"]book[/TD]
[TD="class: xl79, align: right"]4[/TD]

[TD="class: xl71, align: right"]03-04-14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ramesh[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl72, align: right"]5[/TD]

[TD="class: xl71, align: right"]03-04-14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]jagdish[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]book[/TD]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pencil[/TD]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]book[/TD]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pencil[/TD]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pencil[/TD]
[TD="class: xl72, align: right"]6[/TD]

[TD="class: xl71, align: right"]01-04-14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]dinesh[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]7[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl76, align: left"]book[/TD]
[TD="class: xl76, align: right"]7[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]7[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pen[/TD]
[TD="class: xl64, align: right"]7[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pen[/TD]
[TD="class: xl72, align: right"]7[/TD]

[TD="class: xl71, align: right"]02-04-14[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]dinesh[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pencil[/TD]
[TD="class: xl64, align: right"]8[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl64, align: right"]8[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]pencil[/TD]
[TD="class: xl64, align: right"]8[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl78, align: left"]book[/TD]
[TD="class: xl78, align: right"]8[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"]ink[/TD]
[TD="class: xl72, align: right"]8[/TD]

[TD="class: xl73, align: right"]03-04-14[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, align: left"]dinesh[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, align: left"]pen[/TD]
[TD="class: xl74, align: right"]9[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, align: left"]pencil[/TD]
[TD="class: xl74, align: right"]9[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, align: left"]pen[/TD]
[TD="class: xl74, align: right"]9[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, align: left"]ink[/TD]
[TD="class: xl74, align: right"]9[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, align: left"]book[/TD]
[TD="class: xl75, align: right"]9[/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"] 01-04-14
 [/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"] dinesh [/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"] book [/TD]
[TD="class: xl64, align: right"] 10 [/TD]

[TD="class: xl63, align: right"] 02-04-14 [/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"] dinesh [/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: left"] book [/TD]
[TD="class: xl64, align: right"] 24 [/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"]
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

</tbody>

I want table above to be calculated as in table below . thanks for your reply ragards
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top