Index Match Offset Query

[Excel Dumbo]

HI Friends,

Could you please kindly refer the attached worksheet and help frame a formula ? I'm guessing index match is a good one to use. If there is anything easier, would be really great. Thanks heaps

[Ace_XL]

Your data in Data Sheet is not great to work with..
Do this..
Delete (Clear contents) of all non-numeric (text) values in Columns C,D,E,F of Data Sheet.

then in Cell C5 of Sheet 1 use

=SUMPRODUCT((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$600)))*(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$600)))*'Data SHeet'!$C$10:$C$600)/SUMPRODUCT((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$600)))*(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$600)))*'Data SHeet'!$D$10:$F$600)*3*30

Copy down and across

[Excel Dumbo]

HI Ace,

Is there a short cut to delete the non numeric values ? or do i have to manually delete text values ?

Would have been great, if there was a way to go around it, that is, without deleting any values, as i do this report regularly every month, I can copy paste the raw data on to the data sheet every month

[benishiryo]

hi Joseph. to delete the texts,
1. select Column C:F of Data SHeet.
2. press CTRL + G
3. press Special
4. select Constants & check only the Text
5. press OK
6. press DEL

without having to delete, use this array formula in C8:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$523))),--(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$523))),'Data SHeet'!$C$10:$C$523)/

(SUM((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$523)))*(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$523)))*(IF(ISNUMBER('Data SHeet'!$D$10:$F$523),'Data SHeet'!$D$10:$F$523)))/3)*30

my answers are different from yours though, but same with Ace_XL's. so do check. mine will take up more resources & make the file slower, being an array formula. if possible, use Ace_XL's solution. to counter errors:

=IF((SUM((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$523)))*(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$523)))*(IF(ISNUMBER('Data SHeet'!$D$10:$F$523),'Data SHeet'!$D$10:$F$523)))/3)<>0,SUMPRODUCT(--(ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$523))),--(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$523))),'Data SHeet'!$C$10:$C$523)/

(SUM((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$523)))*(ISNUMBER(SEARCH(B$5,'Data SHeet'!$B$10:$B$523)))*(IF(ISNUMBER('Data SHeet'!$D$10:$F$523),'Data SHeet'!$D$10:$F$523)))/3)*30,0)

not sure if you need to separate the formula like in the other thread

[Excel Dumbo]

Hi Benishriyo, Thanks heaps.

Can i use the same formula for columns F to K (no multiply by 30 and all ) ? As they are different to columns B to E (multiply by 30) . As mentioned in the comments in the spreadsheet.
I tried to use the formula and it is giving me differnet answers as you said. i will have a look again.

[Ace_XL]

Can i use the same formula for columns F to K (no multiply by 30 and all ) ? As they are different to columns B to E (multiply by 30) . As mentioned in the comments in the spreadsheet

In G8

=SUMPRODUCT((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$600)))*(ISNUMBER(SEARCH(F$5,'Data SHeet'!$B$10:$B$600)))*'Data SHeet'!$I$10:$I$600)

Delete the non-numeric stuff first though

[Excel Dumbo]

THanks heaps Ace XL and Benishriyo. You made this look easy. Hats off to you both.

I had used both of yours formula to get the answers i wanted. They are magic

one small question, can we build something in to your formula to get ride of the #DIV error i get with Benishriyo's formula (as shown in the attached sheet)

[Excel Dumbo]

Sorry, I missed Benishriyo answer to my above question in Post #4 . Beat me

THanks heaps . THis site and the people who help in here are awesome.