Using AND to check 2 conditions...

**portsample** · 08-04-2011, 01:14 AM

**DonkeyOte** · 08-04-2011, 02:14 AM

I'm running out of the door so have not looked at your file but in terms of Array / SUMPRODUCT functions - in general terms:

You can not use AND/OR functions
ANDs are performed by means of *
ORs are performed by means of +

In your example - an AND based test would look like:

=INDEX(...,SMALL(IF((test1)*(test2),ROW(...)),ROWS(...)))

alternatively - you can use embedded IFs

=INDEX(...,SMALL(IF(test1,IF(test2,ROW(...))),ROWS(...)))

the latter is arguably more efficient but pending version you are limited regards number of embedded IFs you may use.

Quickly glancing at your data set ... if returning numbers (ordered etc...) you don't really need the INDEX as you can use a single SMALL - ie use the Date field rather than Row and apply k to the dates.

Also... Pivot Table(s) ?

**portsample** · 08-05-2011, 07:55 PM

Got it-

The below worked great to parse out the dates
={INDEX(data!$A$1:$A$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2),ROW(data!$A$1:$A$100)),ROWS($B$1:B1)))}

I'm working on copying the count value for each specific incidence of date-type-fuel.

Here is what I have so far working with nested IF statements:
=INDEX(data!$A$1:$A$100,IF(data!$E$1:$E$100=$B$2,IF(data!$C$1:$C$100=$A$2,IF(data!$A$1:$A$100=$C2),ROW(data!$D$1:$D$100)))),ROWS($B$1:B1))))
This would go in D2 on the report tab.

Help. File uploaded.
Thanks in advance.

<data tab>

date | type | fuel | count
6/21/2011 | Blue Volvo | diesel | 13
6/21/2011 | Red Citroen | electric | 12
6/23/2011 | White Landcruiser | gasoline | 11
6/23/2011 | Green Ford | diesel | 12
6/23/2011 | Silver Toyota | electric | 15
6/23/2011 | White Landcruiser | diesel | 12
6/28/2011 | Blue Volvo | corn oil | 17
6/28/2011 | Red Citroen | electric | 19
6/28/2011 | Yellow Mazda | gasoline | 12
6/28/2011 | Green Ford | diesel | 10
7/2/2011 | White Landcruiser | electric | 11
7/2/2011 | Silver Toyota | electric | 12
7/2/2011 | Green Ford | diesel | 15
7/2/2011 | Yellow Mazda | corn oil | 12
7/3/2011 | White Landcruiser | diesel | 23
7/3/2011 | Blue Volvo | gasoline | 12
7/12/2011 | White Landcruiser | electric | 13

<report tab>
type | fuel | date | count
White Landcruiser | electric | 7/2/2011 | 11
| | 7/12/2011 | 13

White Landcruiser | diesel | 6/23/2011 | 12
| | 7/3/2011 | 23

Red Citroen | electric | 6/21/2011 | 12
| | 6/28/2011 | 19

**portsample** · 08-06-2011, 12:21 AM

This,

=INDEX(data!$F$1:$F$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2)*(data!$A$1:$A$100=$C2),ROW(data!$F$1:$F$100)),ROWS($B$1:B1)))

returns only the correct first match for all three conditionals and "#NUM!" thereafter.

**portsample** · 08-06-2011, 12:27 AM

Got it.

{=INDEX(data!$F$1:$F$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2)*(data!$A$1:$A$100=$C2),ROW(data!$F$1:$F$100)),ROWS($B1:B1)))}

Is there a more concise solution?

**DonkeyOte** · 08-06-2011, 03:00 AM

Arrays are inefficient so you should only ever really use in moderation.

Here it would seem as though a Pivot Table would be the perfect tool for analysis - easy to use, flexible and efficient - see attached for working example (Pivot sourced from Dynamic Named Range)

Per your sample this is an XL2007+ file/pivot format.