Hi,
I have an issue whereby below I can solve the first requirement by using a Sumifs(Index) formula as highlighted (sorry unable to upload files due to corporate restrictions).
However, when needing to adapt this formula to look along a second row, with a new criteria, I am a little stuck.
If anyone can adapt this formula it would be much appreciated (or an alternative of course).
Thanks
Ryan
I have an issue whereby below I can solve the first requirement by using a Sumifs(Index) formula as highlighted (sorry unable to upload files due to corporate restrictions).
However, when needing to adapt this formula to look along a second row, with a new criteria, I am a little stuck.
If anyone can adapt this formula it would be much appreciated (or an alternative of course).
Thanks
Ryan
Actual | Actual | Actual | Actual | Actual | Actual | Actual | Forecast | Actual | Forecast | Actual | Forecast | |||
P01 | P02 | P03 | P04 | P05 | P06 | P07 | P07 | P08 | P08 | P09 | P09 | |||
employee 1 | 1000 | 1030 | 1061 | 1093 | 1126 | 1159 | 1194 | 1230 | 1267 | 1305 | 1344 | 1384 | ||
employee 2 | 1267 | 1305 | 1344 | 1384 | 1426 | 1469 | 1513 | 1558 | 1605 | 1653 | 1703 | 1754 | ||
employee 3 | 1546 | 1592 | 1640 | 1689 | 1740 | 1792 | 1846 | 1901 | 1958 | 2017 | 2078 | 2140 | ||
employee 4 | 2321 | 2391 | 2462 | 2536 | 2612 | 2691 | 2771 | 2855 | 2940 | 3028 | 3119 | 3213 | ||
employee 5 | 5678 | 5848 | 6024 | 6205 | 6391 | 6582 | 6780 | 6983 | 7193 | 7409 | 7631 | 7860 | ||
employee 6 | 1290 | 1329 | 1369 | 1410 | 1452 | 1495 | 1540 | 1587 | 1634 | 1683 | 1734 | 1786 | ||
Criteria: | Employee 1 | |||||||||||||
Criteria: | P02 | |||||||||||||
Answer: | 1030 | |||||||||||||
Solution | =SUMIFS(INDEX($F$5:$Q$10,,MATCH($D$15,$F$4:$Q$4,0)),$E$5:$E$10,$D14) | |||||||||||||
Criteria | Employee 4 | |||||||||||||
Criteria | Forecast | |||||||||||||
Criteria: | P07 | |||||||||||||
Answer: | 2855 | |||||||||||||
Solution | ???? |