Multiple Criteria Question 1

[Bronte1226]

Is there are way to pull a field from a spreadsheet based on two criteria matches? I was trying to use the LOOKUP function, but I can only get it to work with one criteria. I really need to use two. Do I have an option to do this?

 

[bluedragon2]

Join the two criteria columns and lookup for the joined criteria.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

or,

If the value being looked up is a number, you can use the SUMPRODUCT formula as illistrated in FAQ68-4725

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Bronte1226]

Well, I can't actually do that. One column used for a criteria is a number for the month in the year, the other is a class code, also a number. The data being pulled is a sum of a bigger set of data. I am needing to pull information for instance if the month is January ("1") and the Class code is "22" then pull the number summed from that row of the dataset.

What I have at the moment is this:

=INDEX('301'!$B$1:$D$250, MATCH(1,'301'!$B$1:$B$250,), MATCH("Sum",'301'!$B$1:$D$1,))

But this only gives me filtering out of the Month not the month and the class like I need.

I tried nesting an if statement after looking at some Microsoft support documents, which did not work. Example of this below.

=INDEX('301'!$B$1:$D$250, MATCH(1,(IF,'301'!$A$1:$A$250=1,'301'!$B$1:$B$250,), MATCH("Sum",'301'!$B$1:$D$1,))

I also need to make the results if there is no match to display a"0" instead of a "#N/A" that I get now, so I can total the numbers.

I hope this makes sense...

 

[bluedragon2]

=SUMPRODUCT((A1:A1000=1)*(B1:B1000=22)*(C1:C1000))

replace the column to match yours. All lookup columns must be the same size. You can change the criteria to cell references.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Wait, That won't work for you since all are numbers, you will get the wrong result.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

"I can't actually do that."

Why not?

Using named range, rather than A1 range notation...
[tt]
=sumproduct((MON=1)*(CLASS=22)*(NumberValues))
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bluedragon2]

wait it will work, thinking of another function.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Maybe some sample data will help CLARIFY what you're dealing with.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Bronte1226]

Well, I couldn't get the productsum solution to work. Here is a sample of the data...the months go through 12 of course and there are 4 worksheets I am working with, all with the same type of data, in different groupings (values of the summed and counted columns) In all there is about 1000 summarized records imported in from view created in SQL representing 100K+ raw data records...

Month Class Sum Count
1 3 180 3
1 6 730 16
1 15 110 2
1 16 290 10
1 22 43760 845
1 25 620 12
1 30 290 5
1 35 19050 328
1 36 1120 21
1 40 350 5
1 42 10 1
1 46 160 2
1 47 110 2
1 50 420 7
1 55 50 1
1 76 540 12
1 88 740 25
2 3 80 1
2 6 650 16
2 15 100 2
2 16 320 9
2 22 43730 855
2 25 340 7
2 30 340 7
2 35 16050 281
2 36 1130 22
2 40 380 6
2 42 90 3
2 46 110 2
2 47 190 3
2 50 140 2
2 55 40 2
2 75 30 1
2 76 350 6
2 88 610 20
2 98 250 4

 

[SkipVought]

FIRST, I would advise against using MONTH. CLASS, SUM, COUNT as field headings. These are RESERVE WORDS.

I modified the headings,and named the ranges accordingly...
[tt]
MON Cls SM CT
[/tt]
my formula...
[tt]
=SUMPRODUCT((MON=1)*(Cls=22)*(SM))
[/tt]
returns
[tt]
43760
[/tt]
It WORKS! SIMPLE!!!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Bronte1226]

That did it! Worked beautifully, THANK YOU!!!!