Problem with Sumifs and Multiple Criteria

[redgrace]

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.

 

[Aladin Akyurek]

again sorry it says error in formula
thanks

An errand = sign that needs to be corrected for.

=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)

 

[dockhem]

pardon

 

[Aladin Akyurek]

pardon

I had an equal sign in the last term. That should be a comma. See my last reply.

 

[dockhem]

I had an equal sign in the last term. That should be a comma. See my last reply.

sorry , i tried these things already did not work. it want parenthesis to correct error
thanks

 

[Aladin Akyurek]

sorry , i tried these things already did not work. it want parenthesis to correct error
thanks

I'm going to cry. My bad...

=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)

I don't think there is any other glitch. ray:

 

[dockhem]

I'm going to cry. My bad...

=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)

I don't think there is any other glitch. ray:

thanks now it works . meanwhile i tried this =IFERROR(SUM(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)))),0) is it wrong formula ? or both are equivalent ?

 

[dockhem]

when i use C S E it gives right answer which is 101, when i use enter key it gives 3131 , why ?

 

[Aladin Akyurek]

thanks now it works . meanwhile i tried this =IFERROR(SUM(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)))),0) is it wrong formula ? or both are equivalent ?

when i use C S E it gives right answer which is 101, when i use enter key it gives 3131 , why ?

SumIfs is faster than an array formula with SUM processing the IF evaluations.

Array formulas require control+shift+enter. If ommitted, SUM won't see the full set of evaluations.

Suppose an intermediate evaluation like

{12;0;24}

SUM would get, using only enter, just 12 to see, not all values.

 

[dockhem]

thanks for reply
regards

 

[Aladin Akyurek]

thanks for reply
regards

You are welcome.