?
Solved

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

Posted on 2011-10-20
2
Medium Priority
?
156 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:KingG69
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 36999009
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
 

Author Closing Comment

by:KingG69
ID: 36999101
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question