Incremental value search

[alkamalju]

Well, I have a workbook named “January”. Hour 1 for example will repeat many a times in the column A (Day 1). I want to summarize per hour sum (total) value on the Final Sheet.

Sheets are formatted like:

Sheet1: Day 1

A B
Hour Value

1 2.0
2 2.1
3 4.1
.
.
24 3.4
1 0.1
2 2.2

I have similar 30 more sheets for each day.

Now, on the Final Sheet, what I am expecting is like:

A B C

1. Hour 1 2…….24

2. Day 1:

3 Day 2:

4 Day 3:

Now, I want to do the following:

i want this value will come from the sheet 1 where it will find the sum value against hour1. Upto this part is ok =SUMIFS('Sheet1 (2)'!A:A,'Sheet1 (2)'!B:B,"1:00"). However, I was thinking how I can tell excel to search for for 2.00 when it will be searched for hour “2” instead of 1. What should be the formula?

Optional:

Similar question comes for day 2 (Field=B3 on Final sheet), I should tell excel to take data for 'Sheet2 instead of 'Sheet1 automatically without manual edit.

How complex it will be even if I think to make a formula on B2 which will be suitable for All cel

[DonkeyOte]

Assuming the values in A correspond to the sheet names (exactly) then in theory:

B2: =SUMIF(INDIRECT("'"&$A2&"'!A:A"),B$1,INDIRECT("'"&$A2&"'!B:B"))

Copy across the matrix to get your results.

(A2:Ax will hold the sheet name and row 1 (B onwards) holds the hour)