match/lookup/find -fuction that returns TRUE/FALSE

[Richard]

Hi
I am looking for a function that matchs an array with an
text string and return a TRUE/FALSE if found or not.

The background is that I have a very large amount of data.
The data is contain "Type", "Year/month", "Number of"
and "Cost".

I have already made some calculations in order to sum the
cost during a "Year" for this particular "Type". This was
easy (after a tip from this forum ) by using the
=SUMPRODUCT-function. Like =SUMPRODUCT((Type=xxx)*(Year=nn)
*(Cost)).

What I need to do now is to sum the cost per month/year if
the type is one of 8 particular types.

Does this make any sense??

Hope someone has done something similar and can give me a
tip!

Best regards
Richard

 

[Frank Kabel]

Hi
ion your case some ideas.
1. Hardcoded types:
=SUMPRODUCT((Type={xxx,yyy,zzz,www})*(Year=nn)*(Cost))


2. You have stored your types in another range. e.g. in
the cells X1:X8. In this case enter the following formula
as array formula (with CTRL+SHIFT+ENTER):
=SUMPRODUCT((Type=TRANSPOSE(X1:X8))*(Year=nn)*(Cost))

 

[Aladin Akyurek]

=SUMPRODUCT(--ISNUMBER(MATCH(TypeRange,ConditionList,0)),--(YearMonthRange=Y
earMonthCondition),CostRange)

where ConditionList refers to a range that houses 8 types of interest.