Need to sum dollars if name listed more than once in source range

[Jbooze]

This formula is working well with on major exception. A person can work in more than one department and their name will be listed more than once. This formula will only capture the dollars of the first matching name entry. If John Doe is a match in the Jan-Pay Paste tab more than once, I need it to sum the column 3 matches in the range for this name. Is there a way to modify this formula to do this?

=IFNA(IF(B3="","",IF(C3="","Enter Name",IF('PFP Goals'!$C$39=1,(VLOOKUP($C3,'Jan-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=2,(VLOOKUP($C3,'Feb-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=3,(VLOOKUP($C3,'Mar-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=4,(VLOOKUP($C3,'Apr-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=5,(VLOOKUP($C3,'May-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=6,(VLOOKUP($C3,'Jun-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=7,(VLOOKUP($C3,'Jul-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=8,(VLOOKUP($C3,'Aug-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=9,(VLOOKUP($C3,'Sep-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=10,(VLOOKUP($C3,'Oct-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=11,(VLOOKUP($C3,'Nov-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=12,(VLOOKUP($C3,'Dec-Pay Paste'!$C$4:$Z$498,3,FALSE)),0)))))))))))))),0)

Thanks,

Jeff

 

[markmzz]

This formula is working well with on major exception. A person can work in more than one department and their name will be listed more than once. This formula will only capture the dollars of the first matching name entry. If John Doe is a match in the Jan-Pay Paste tab more than once, I need it to sum the column 3 matches in the range for this name. Is there a way to modify this formula to do this?

=IFNA(IF(B3="","",IF(C3="","Enter Name",IF('PFP Goals'!$C$39=1,(VLOOKUP($C3,'Jan-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=2,(VLOOKUP($C3,'Feb-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=3,(VLOOKUP($C3,'Mar-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=4,(VLOOKUP($C3,'Apr-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=5,(VLOOKUP($C3,'May-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=6,(VLOOKUP($C3,'Jun-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=7,(VLOOKUP($C3,'Jul-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=8,(VLOOKUP($C3,'Aug-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=9,(VLOOKUP($C3,'Sep-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=10,(VLOOKUP($C3,'Oct-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=11,(VLOOKUP($C3,'Nov-Pay Paste'!$C$4:$Z$498,3,FALSE)),IF('PFP Goals'!$C$39=12,(VLOOKUP($C3,'Dec-Pay Paste'!$C$4:$Z$498,3,FALSE)),0)))))))))))))),0)

Thanks,

Jeff

Hi!

Maybe the suggestion below can helps.

=IF(B3="","",IF(C3="","Enter Name",SUMIF(CHOOSE('PFP Goals'!$C$39,'Jan-Pay Paste'!$C$4:$C$498,'Feb-Pay Paste'!$C$4:$C$498),$C3,
CHOOSE('PFP Goals'!$C$39,'Jan-Pay Paste'!$E$4:$E$498,'Feb-Pay Paste'!$E$4:$E$498))))

Markmzz

 

[Jbooze]

I am not understanding this formula. If C39=1 then the Jan-Pay Paste tab should be searched and if C3 is found multiple times, I need to sum the column for regular dollars and the column for OT dollars for the name that was listed more than once in that tab. How does this formula confirm that C39=1, 2, 3 etc. before choosing which tab to search?

Thanks,

Jeff

 

[markmzz]

I am not understanding this formula. If C39=1 then the Jan-Pay Paste tab should be searched and if C3 is found multiple times, I need to sum the column for regular dollars and the column for OT dollars for the name that was listed more than once in that tab. How does this formula confirm that C39=1, 2, 3 etc. before choosing which tab to search?

Thanks,

Jeff

I just did the formula for January and February. To make the calculation for the remaining months, just add the other 10 months in the Choose function.

Here is a example with Mar:

=IF(B3="","",IF(C3="","Enter Name",SUMIF(CHOOSE('PFP Goals'!$C$39,'Jan-Pay Paste'!$C$4:$C$498,'Feb-Pay Paste'!$C$4:$C$498,'Mar-Pay Paste'!$C$4:$C$498),$C3,
CHOOSE('PFP Goals'!$C$39,'Jan-Pay Paste'!$E$4:$E$498,'Feb-Pay Paste'!$E$4:$E$498,'Mar-Pay Paste'!$E$4:$E$498))))

For more details about Choose function look at the link below.

https://support.office.com/en-us/article/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc

Markmzz

 

[markmzz]

By the way, you can try this too:

=IF(B3="","",IF(C3="","Enter Name",SUMIF(INDIRECT("'"&TEXT(1*('PFP Goals'!$C$39&"/2018"),"mmm")&"-Pay Paste'!C4:C498"),$C3,
INDIRECT("'"&TEXT(1*('PFP Goals'!$C$39&"/2018"),"mmm")&"-Pay Paste'!E4:E498"))))

Markmzz