match, Index, If

[Shortstopper00]

I have a data set that is user based. The answer that i want to bring up is
dependant on 3 variables. The first is the month it is (column), second is
the type of day sunny or cloudy (cloumn) and the third is the time of day
which is in a row. If anyone could help me with a code taht could be used it
would be greatly appreciated.
1 2 3
April Sunny 3.7 3.2 2.8
April Cloudy 4.1 3.7 3.2
July Sunny 16.3 15.7 15.2
July Cloudy 18.6 18.2 17.5
October Sunny 6.9 6.1 5.7
October Cloudy 7.2 6.7 6.1

 

[Shane Devenshire]

Hi,

Here is the basic idea:

=SUMPRODUCT(--(A$2:A$16>=C1),--(B$2:B$16=C2),--($D$1:$R$1=C3)

In this example column A contains date, B contains Cloudy/Sunny, and range
D1:R1 contains your third item. The values you want to count for each of
these are in C1, C2, and C3. You could hard code these, ie. instead of C1
you could type "March".

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shortstopper00]

Hey, I used the code was helpful, but where do I index the values that I want
come up. after the 3 variables are picked by the user

 

[Shane Devenshire]

Hi,

I don't understand you question. If you want to copy the formula down and
use other values rather than C1, C2 and C3 then you could put the three
values in three empty columns (say C1:E1) and then each new combination below
them then copy the formula down

=SUMPRODUCT(--(A$2:A$16>=C1),--(B$2:B$16=D1),--($F$1:$T$1=E1)

Note: I have moved the last range so it doesn't overlap the C1:E1 range. I
don't know where your ranges are actually located.

If this helps please click the Yes button.

Cheers,
Shane Devenshire

 

[Max]

Ah, darned, you multi-posted in .misc. Pl refrain from doing this
Below's my response to you over there:
----- ----
Assume your reference table as posted is in A1:E7

Assume the triple inputs in G2:I2 are: October, Sunny, 2
In J2, normal ENTER:
=INDEX(C2:E7,MATCH(1,INDEX((A2:A7=G2)*(B2:B7=H2),),0),MATCH(I2,C1:E1,0))
will return the reading 6.1

Changing the triple inputs to: July, Sunny, 3
will yield in J2: 15.2

Adapt the ranges to suit
------ ----
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

 

[Shortstopper00]

Ok I'll explain alittle better what I'm trying to do. OK on the first sheet
there is a bunch of questions being asked to the user, What Month it is (
B2-B7). The next question is What type of day it is Cloudy or Sunny (C2-C7).
These variable repeat because of the different months. The next question they
have is the time of the day (D1 -AA1). Now based on these variables I want a
temperature that corresponds with these variables to pop up into another box.
Now this temperature changes with the different options the user picks. So
say he picks october, cloudy, and 3 am the value 6.1 will come up in this box.
This is a portion of the data set

1 2 3 4 5
April Sunny 3.7 3.2 2.8 2.4 1.6
April Cloudy 4.1 3.7 3.2 2.6 2
July Sunny 16.3 15.7 15.2 14.6 14.1
July Cloudy 18.6 18.2 17.5 16.2 15.7
October Sunny 6.9 6.1 5.7 5.1 4.5
October Cloudy 7.2 6.7 6.1 5.8 5.6

 

[T. Valko]

Assuming the time of day is entered as an integer representing the hour.
That's what it looks like in your sample data.

B10 = lookup month
C10 = type of day (cloudy, sunny)
D10 = hour of day

=SUMPRODUCT(--(B2:B7=B10),--(C2:C7=C10),INDEX(D2:AA7,,D10))