Wednesday, February 27, 2013

yogi_Compute Sum Of Hits In Column C For Each Number In Column A And Date In Row 5


                                          Google Spreadsheet   Post  #1056
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user jmitquestion said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/6KqqlRZXTlU)
Index Match Match - Works on 1st Column, Rest fail....Confused 
Hi

I am trying to run a relatively simple index, match, match on a range of data. 

I have written the following formula and it works in the first column
=INDEX(FCF2!$C$13:$C$132,MATCH($A2,FCF2!$A$13:$A$132,0),MATCH(B$1,FCF2!$B$13:$B$132,0))

However when I move the formula to the right it fails and i get the error message error: Reference out of range

My raw data is laid out as follows

Column A - Number - Value between 1-5
Column B - Date
Column C - Any Value

Raw Data
Number date HIts
1 2/4/2013 751
1 2/5/2013 3227
1 2/6/2013 3016
1 2/7/2013 3123
1 2/8/2013 2761
1 2/9/2013 1404
2 2/4/2013 7511
2 2/5/2013 31227
2 2/6/2013 30216
2 2/7/2013 31213
2 2/8/2013 27361
2 2/9/2013 14404
3 2/4/2013 7512
3 2/5/2013 32
3 2/6/2013 30
3 2/7/2013 323
3 2/8/2013 231
3 2/9/2013 14434
4 2/4/2013 7432
4 2/5/2013 3423
4 2/6/2013 312312
4 2/7/2013 344543
4 2/8/2013 232
4 2/9/2013 140



DATA OUTPUT
2/4/2013 2/5/2013 2/6/2013
1 751 #REF! #REF!
2 8511 #REF! #REF!
3 7512 #REF! #REF!
4 7432 #REF! #REF!
5 13 #REF! #REF!

Any help would be appreciated

Cheers

John
----------------------------------------------------------------------------------------------------------

following is a solution to the problem