Nesting two separate formulas

[reneev]

I've been working on this for an hour. I don't know what I'm missing. In lay-terms what I'm trying to say is (color in text corresponds to color in formula):

If Cell J2 equals "variable" and the date in Cell C2 is equal to or greater than the date in Q1, (Value if true) then look for the Employee ID on this sheet and find it on that sheet then sum all the hours for that person on that sheet, (Value if false) If NOT then just leave a space.

=IF(AND(J2="variable",C2>=$Q$1,SUMIF(A:A,'Pay Period hours'!A:A,'Pay Period hours'!E:E)," ")))

What am I missing or doing wrong?

 

[Eric W]

Some parentheses out of place, and your SUMIF had the parameters reversed. If A2 is where the Employee ID is then try:

=IF(AND(J2="variable",C2>=$Q$1),SUMIF('Pay Period hours'!A:A,A2,'Pay Period hours'!E:E)," ")

 

[reneev]

Some parentheses out of place, and your SUMIF had the parameters reversed. If A2 is where the Employee ID is then try:

=IF(AND(J2="variable",C2>=$Q$1),SUMIF('Pay Period hours'!A:A,A2,'Pay Period hours'!E:E)," ")

ok, that got rid of the error, but it should have resulted in the calculation and it's resulting in a space/

 

[Eric W]

If it's a space, then the AND function must not be true. So either J2 is not equal to "variable", or C2 is not greater than Q1. J2 should be easy to check, but make sure there's no hidden spaces. And for the date comparison, make sure that the dates are both stored as dates (a number), and not a date stored as text.

 

[reneev]

FOUND IT!! There was an extra comma after $Q$1

=IF(AND(J1122="variable",C1122>=$Q$1,),SUMIF('Pay Period hours'!A:A,A1122,'Pay Period hours'!E:E)," ")

Thank you!