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.
 
Sort by date Sort by votes
dockhem said:

<tbody>
[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
Click to expand...

Control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$A$9=A12,IF($I$2:$I$9=I12,IF($N$2:$Z$9=N12,$O$2:$AA$9))))

where A12 houses 01-04-14, I12 dinesh as value, and N12 book as value.
 
Last edited:
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Aladin Akyurek said:
Control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$A$9=A12,IF($I$2:$I$9=I12,IF($N$2:$Z$9=N12,$O$2:$AA$9))))

where A12 houses 01-04-14, I12 dinesh as value, and N12 book as value.
Click to expand...

Thank you vary much, it worked nice. i entered different row number.
regards,
dockhem
 
Upvote 0
=IF('(1)'!$A$5:$A$20905=$A5,IF('(1)'!$I$5:$I$20905=$AI$7,IF('(1)'!$L$5:$L$20905='(1)'!$BK$15,'(1)'!$J$5:$J$20905,0))) and =IF('(1)'!$A$5:$A$20905=$A5,IF('(1)'!$I$5:$I$20905=$AI$7,IF('(1)'!$L$5:$L$20905='(1)'!$BK$15,'(1)'!$J$5:$J$20905))),0) results into FALSE not 0, i could not find why ? even after several tries. please help me . thanks
regards
dockhem
 
Upvote 0
dockhem said:
=IF('(1)'!$A$5:$A$20905=$A5,IF('(1)'!$I$5:$I$20905=$AI$7,IF('(1)'!$L$5:$L$20905='(1)'!$BK$15,'(1)'!$J$5:$J$20905,0))) and =IF('(1)'!$A$5:$A$20905=$A5,IF('(1)'!$I$5:$I$20905=$AI$7,IF('(1)'!$L$5:$L$20905='(1)'!$BK$15,'(1)'!$J$5:$J$20905))),0) results into FALSE not 0, i could not find why ? even after several tries. please help me . thanks
regards
dockhem
Click to expand...

Such a formula does not return a single value (either FALSE or 0), but rather 20905-5+1 values. What do you have in mind?
 
Upvote 0
Aladin Akyurek said:
Such a formula does not return a single value (either FALSE or 0), but rather 20905-5+1 values. What do you have in mind?
Click to expand...

=IF i want sum of '(1)'!$J$5:$J$20905 if these three ('(1)'!$A$5:$A$20905=$A5,IF('(1)'!$I$5:$I$20905=$AI$7,IF('(1)'!$L$5:$L$20905='(1)'!$BK$15) criteria are fulfilled . if any of these three is negative result should be zero.
thanks for reply
regards
dockhem
 
Upvote 0
dockhem said:
=IF i want sum of '(1)'!$J$5:$J$20905 if these three ('(1)'!$A$5:$A$20905=$A5,IF('(1)'!$I$5:$I$20905=$AI$7,IF('(1)'!$L$5:$L$20905='(1)'!$BK$15) criteria are fulfilled . if any of these three is negative result should be zero.
thanks for reply
regards
dockhem
Click to expand...
Looks like...
Rich (BB code): 
=SUMIFS(
    '(1)'!$J$5:$J$20905
    '(1)'!$J$5:$J$20905>0,
    '(1)'!$A$5:$A$20905,$A5,
    '(1)'!$I$5:$I$20905,$AI$7,
    '(1)'!$L$5:$L$20905='(1)'!$BK$15)
 
Upvote 0
Aladin Akyurek said:
Looks like...
Code: 
[SIZE=2][FONT=lucida console][B]=SUMIFS(
    '(1)'!$J$5:$J$20905
    '(1)'!$J$5:$J$20905>0,
    '(1)'!$A$5:$A$20905,$A5,
    '(1)'!$I$5:$I$20905,$AI$7,
    '(1)'!$L$5:$L$20905='(1)'!$BK$15)
[/B][/FONT][/SIZE][/code
sorry it did not work.it say you entered too few arguments
Click to expand...
 
Upvote 0
dockhem said:
Aladin Akyurek said:
Looks like...
Rich (BB code): 
=SUMIFS(
    '(1)'!$J$5:$J$20905
    '(1)'!$J$5:$J$20905>0,
    '(1)'!$A$5:$A$20905,$A5,
    '(1)'!$I$5:$I$20905,$AI$7,
    '(1)'!$L$5:$L$20905='(1)'!$BK$15)
[/code
sorry it did not work.it say you entered too few arguments
Click to expand...
Rich (BB code): 
I'm sorry too. We are just missing a comma...

	
	
	
	
	
	




	

		Rich (BB code):

	

	

		

=SUMIFS(     
  '(1)'!$J$5:$J$20905,
  '(1)'!$J$5:$J$20905>0,
  '(1)'!$A$5:$A$20905,$A5,
  '(1)'!$I$5:$I$20905,$AI$7,
  '(1)'!$L$5:$L$20905='(1)'!$BK$15)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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