Prev: Formula to fill one cell from another
Next: How to use autofilter in excel with formula reference changing
From: Another wildcard and array problem Another wildcard and array on 27 Apr 2010 15:08
I need to find a solution for the following formula to be used in an Excel
worksheet in Excel 2003:

=SUM((('GCP Vendors'!L3:L999)="*Other*")*(('GCP Vendors'!O3:O999)=
"Consultation"))

I need to count the number of times a cell contains the partial string
"Other" in the L column and then sum up how many of these strings are related
to "Consultation" in the O column. I can't figure out how to substitute for
"*Other*" in an array.

Thanks,
GP
From: Dave Peterson on 27 Apr 2010 15:40
One way is to use
=isnumber(search(...))

=SUMPRODUCT(--(ISNUMBER(SEARCH("other",'gcp vendors'!L3:L999))),
--('gcp vendors'!O3:O999>="Consultation"))

Using =sumproduct() means that I don't have use ctrl-shift-enter to enter the
formula.

And I like the -- and commas syntax.

But

=SUM((ISNUMBER(SEARCH("other",'gcp vendors'!L3:L999))),
*('gcp vendors'!O3:O999>="Consultation"))

will work as an array formula.

Ps. If you want to match case, then use =find() instead of =search().

Another wildcard and array problem wrote:
>
> I need to find a solution for the following formula to be used in an Excel
> worksheet in Excel 2003:
>
> =SUM((('GCP Vendors'!L3:L999)="*Other*")*(('GCP Vendors'!O3:O999)=
> "Consultation"))
>
> I need to count the number of times a cell contains the partial string
> "Other" in the L column and then sum up how many of these strings are related
> to "Consultation" in the O column. I can't figure out how to substitute for
> "*Other*" in an array.
>
> Thanks,
> GP

--

Dave Peterson
From: Tom Hutchins on 27 Apr 2010 15:51
Try

=SUMPRODUCT(--(ISNUMBER(FIND("Other",L3:L999))),--(O3:O999="Consultation"))

Hope this helps,

Hutch

"Another wildcard and array problem" wrote:

> I need to find a solution for the following formula to be used in an Excel
> worksheet in Excel 2003:
>
> =SUM((('GCP Vendors'!L3:L999)="*Other*")*(('GCP Vendors'!O3:O999)=
> "Consultation"))
>
> I need to count the number of times a cell contains the partial string
> "Other" in the L column and then sum up how many of these strings are related
> to "Consultation" in the O column. I can't figure out how to substitute for
> "*Other*" in an array.
>
> Thanks,
> GP
 | 
Pages: 1
Prev: Formula to fill one cell from another
Next: How to use autofilter in excel with formula reference changing