match multiple criteria in a dynamic range

**muchado77** · 11-22-2007, 10:51 PM

I'm trying build a formula in a dynamic report. The purpose of the function is to identify one of "three groups" in a dynamic range. There can be anything from 1 to 50 items in the column.

At the end of each project I want to insert the formula (shown below "formula here!!") its on a SUBTOTAL line which I'm using to calculate $ columns elsewhere.

PROJECTA
Apple
Apple
Vegetable
Matched VC's
--------------------------
+ Formula here!!
--------------------------
PROJECTB
Vegetable
Matched VC's
--------------------------
+ Formula here!!
--------------------------

When the list has one or many different fruit or vegetables - I want to insert the word -- LEVY. eg.

Orange
Orange
Pear
-----------------
LEVY
-----------------

When the list has one or many different fruit or vegetables AND also has one or many "Matched VC's" - I want to say -- LEVY & VC

Orange
Apple
Matched VC's
Matched VC's
-----------------
LEVY & VC
-----------------

When the list has one or many "Matched VC's" but no fruit or veg - I want to say -- VC ONLY

Matched VC's
Matched VC's
-----------------
VC ONLY
-----------------

I have attempted to use INDEX / MATCH, SUMPRODUCT in an IF statement -- but I am getting no where... and too embarrased to post up my examples? Can anyone advise on the function that might be appropriate or offer some guidance?

Thanks and kind regards,

Muchado77

**Lotus123** · 11-27-2007, 05:44 PM

Lets presume your fruit/vege/vc list is in cells A1:A5...here is the formula I would use for the subtotal of that range:

=IF(ISNA(MATCH("Matched VC's",A1:A5,FALSE)),"LEVY",IF(COUNTA(A1:A5)-COUNTIF(A1:A5,"Matched VC's")=0,"VC ONLY","LEVY & VC"))