Looking up when there is more than 1 result and returning results from different part of spreadsheet!

[LauraPlumb]

Hi all,

Apologies for the long title it is a difficult one to explain but here goes:

I have a max value. What i want it to do is find the occurances for the max value along the same row, look up to see which week (number1-47) they occured in (row above), look at a different part of the spreadsheet and add up the values that appear on the same line under the found week numbers.

In terms of occurances the max value ranges from 1 - 20 times on different rows.

An example of the range of cells would be:

Max value in E6
Area to lookup max in: DG6:EZ6
Week Headings: DG3:EZ3

Area to lookup the week heading found above:
06:BI6

Any results it finds i need it to add them up and put them into cell J6

Bearing in mind the spreadsheet has 5616 rows i would like something that will just copy down.

Hope this makes sense - any help would be greatly appreciated =)

 

[venkat1926]

this expression is not clear
?
'"i need it to add them up and put them into cell J6"
sumuip what?????
suppose there are 6 weeks the max is found then the sum up is 6xmax,.value if max value is 25 J6 shoudl have 150
is it so???

try this macro

Sub test()
Dim x As Double, j As Integer, r As Range
x = Range("E6")
Set r = Range("DG6:EZ6")
j = WorksheetFunction.CountIf(r, x)
If j = 0 Then MsgBox "this max is not available"
Range("J6") = j * x
End Sub