Forum Discussion
Solved
Pairing data when having multiple different values in the same row
Hello dear community,
I'm having a problem with a new report where I'm trying to pair payroll codes with their number of hours per personnel number. The regular and overtime hours are always in the same columns, so no problem there, but the others extra codes appears one after the other on the same row associated with the personnel number and it's not always the same.
I'm trying to recreate a simpler table in order to have the hours of the payroll codes by personnel number.
Could you please help me find the best function or formula for this?
The report with raw data
Where I would like to have the results
=SUMPRODUCT(IF(($B$2:$H$12=B$14)*($A$2:$A$12=$A15),$C$2:$I$12))
You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell B15 and filled across range B15:I25.
=SUMPRODUCT(IF(($B$2:$H$12=B$14)*($A$2:$A$12=$A15),$C$2:$I$12))
You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell B15 and filled across range B15:I25.
