PDA

View Full Version : Sum using match feature with multiple criteria



Melinda
04-23-2007, 08:34 AM
I would like to write a function for Column P that SUMS each employees payroll(from the payroll sheet using the SSN to match) from the start date indicated in the "Good Data" sheet and continuing for the number of payperiods(PPDs) indicated.

For example,
Christine Mills pay would start with 06/02/06 and continue for 13 Payperiod(PPDs). Hence her salary would be $2,402.60. However, Joe Black pay would start 12/17/06 and continue for 3 Payperiods(PPDs). Joe Black salary would equal $531.24

I have written several index, match and sumif functions but nothing is working. I have attached a sample worksheet. Thanks very, very much in advance:banghead:

vonpookie
04-23-2007, 10:21 AM
I doubt this is the "best" formula, but it does seem to be working for me:

=SUMPRODUCT(--(Payroll!$E$1:$AB$1>=$N3),--(Payroll!$E$1:$AB$1<=$O3),INDIRECT("Payroll!$E"&MATCH($E3,Payroll!$D$1:$D$16,0)&":$AB"&MATCH($E3,Payroll!$D$1:$D$16,0)))

Shazam
04-23-2007, 11:00 AM
Input formula in cell Q3 and copy down.

=SUMPRODUCT((Payroll!$E$1:$AB$1>=$N3)*(Payroll!$E$1:$AB$1<=$O3)*(Payroll!$D$4:$D$16='Good Data'!E3)*(Payroll!$E$4:$AB$16))


Hope it helps!

asingh
04-23-2007, 07:26 PM
Hi,

paste this in P3 on the "Good Data" Tab, and drag down:

=SUM(INDIRECT(ADDRESS(MATCH((MID(D3,FIND(",",D3,1)+2,100) & " " &LEFT(D3,FIND(",",D3,1)-1)),Payroll!$C$4:$C$16,0)+3,MATCH(N3,Payroll!$E$1:$AB$1,0)+4,1,TRUE,"Payroll")&":"&ADDRESS(MATCH((MID(D3,FIND(",",D3,1)+2,100) & " " &LEFT(D3,FIND(",",D3,1)-1)),Payroll!$C$4:$C$16,0)+3,MATCH(N3,Payroll!$E$1:$AB$1,0)+J3+3)))


You will get some #NA values...because all the names were not exact matches....!...which can be corrected by retyping their names..or using an iserror to trap the entries..

regards,
asingh

Bob Phillips
04-24-2007, 12:59 AM
For some reason VBAX has been adding a space to the formulae. Shaz's should be

=SUMPRODUCT((Payroll!$E$1:$AB$1>=$N3)*(Payroll!$E$1:$AB$1<=$O3)*
(Payroll!$D$4:$D$16='Good Data'!E3)*(Payroll!$E$4:$AB$16))