How can I populate a table with hours based on 2 criteria?

Hi

I have a table of data which I need to parse based on 2 specified criteria and populate a graph table. The problem is that one field that is parsed is in a column and the other in a row, at the intersection is the data I want to sum. I have tried sumproduct, various array formula etc but cannot get it right.

You will see in the attached the first criteria is CstCtr and the other is the month end date, the graph sheet has CstCtr '3810' in field A49 and the month end dates are in row 45, the DataBaseFcst sheet has the data. I hope I have made my question clear, but I think if you check the attached it should be clear.

Thanks   BaseForecast.xls
KingG69Asked:
Who is Participating?
 
Rory ArchibaldCommented:
In B49:

=SUMIF(DataBaseFcst!$A:$A,$A49,INDEX(DataBaseFcst!$D:$AP,0,MATCH(B$45,DataBaseFcst!$D$1:$AP$1,0)))
 and copy across and down as required.
0
 
KingG69Author Commented:
Wow, thanks I have been struggling with this for a couple of hours. In the end it was a simple solution,thanks once again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.