Using a wild card * with Sumproduct

[jeffsmarketplace]

All,

I am using a sumproduct formula to total a number of specific
instances and I would like to include a few variables using a wild
card. As an example:


This is the formula I am using

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-
prd-std"))

I would like to do this

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))

or this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-
prd-std","s1-prd-std-ce","s1-prd-std-V6"))

The problem is none of them work, but the first one. The data is
dynamic and changes from time to time, so any of the 3 or more could
be necessary for me to get an accurate count. Please advise on what to
do.

regards,
Jeff

 

[Jim Cone]

Not very pretty, but...

=SUMPRODUCT(--(B$7:B$39=E31),--(F$7:F$39="s1-prd-std"))
+SUMPRODUCT(--(B$7:B$39=E31),--(F$7:F$39="s1-prd-std-ce"))
+SUMPRODUCT(--(B$7:B$39=E31),--(F$7:F$39="s1-prd-std-V6"))

(I would be inclined to divide the formula into 3 separate cells)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"jeffsmarketplace" <[email protected]>
wrote in message
All,
I am using a sumproduct formula to total a number of specific
instances and I would like to include a few variables using a wild
card. As an example:
This is the formula I am using
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-prd-std"))

I would like to do this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1*"))

or this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-
prd-std","s1-prd-std-ce","s1-prd-std-V6"))

The problem is none of them work, but the first one. The data is
dynamic and changes from time to time, so any of the 3 or more could
be necessary for me to get an accurate count. Please advise on what to
do.
regards,
Jeff

 

[Ms-Exl-Learner]

**************
Question-1:-
**************
I would like to do this

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))

Use the Search function in your sumproduct formula like the below. If
the word is case sensitive then just replace the search to FIND.
Because find function is case sensitive. At the same time search will
accept wild card but find wont allow wildcard.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*(ISNUMBER(SEARCH("S1",'Real
Monitor'!F$7:F$39))))


**************
Question-2:-
**************

[or this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1-
prd-std","s1-prd-std-ce","s1-prd-std-V6"))]

Just mention the text inside the array notion that is {} like the
below will do the work which you are expecting to do.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39={"s1-prd-std","s1-prd-std-ce","s1-prd-std-V6"}))

Hope it's clear to you!

 

[jeffsmarketplace]

**************
Question-1:-
**************
I would like to do this

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))

Use the Search function in your sumproduct formula like the below.  If
the word is case sensitive then just replace the search to FIND.
Because find function is case sensitive.  At the same time search will
accept wild card but find wont allow wildcard.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*(ISNUMBER(SEARCH("S1",'Real
Monitor'!F$7:F$39))))

**************
Question-2:-
**************

[or this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1-
prd-std","s1-prd-std-ce","s1-prd-std-V6"))]

Just mention the text inside the array notion that is {} like the
below will do the work which you are expecting to do.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39={"s1-prd-std","s1-prd-std-ce","s1-prd-std-V6"}))

Hope it's clear to you!

------------------------
Ms-Exl-Learner
------------------------

All,

I am using a sumproduct formula to total a number of specific
instances and I would like to include a few variables using a wild
card. As an example:

This is the formula I am using

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-
prd-std"))

I would like to do this

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))

or this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-
prd-std","s1-prd-std-ce","s1-prd-std-V6"))

The problem is none of them work, but the first one. The data is
dynamic and changes from time to time, so any of the 3 or more could
be necessary for me to get an accurate count. Please advise on what to
do.

regards,
Jeff

Thank you that works, but I ended up using this method instead.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=F40)*(ISNUMBER(SEARCH("S2",'Real
Monitor'!F$7:F$39))))

Jeff

 

[Ms-Exl-Learner]

You are welcome and thanks for feeding back!

------------------------
Ms-Exl-Learner
------------------------

**************
Question-1:-
**************
I would like to do this

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))

Use the Search function in your sumproduct formula like the below.  If
the word is case sensitive then just replace the search to FIND.
Because find function is case sensitive.  At the same time search will
accept wild card but find wont allow wildcard.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*(ISNUMBER(SEARCH("S1",'Real
Monitor'!F$7:F$39))))

**************
Question-2:-
**************

[or this
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1-
prd-std","s1-prd-std-ce","s1-prd-std-V6"))]

Just mention the text inside the array notion that is {} like the
below will do the work which you are expecting to do.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39={"s1-prd-std","s1-prd-std-ce","s1-prd-std-V6"}))

Hope it's clear to you!
On Aug 27, 8:28 am, jeffsmarketplace <[email protected]>
wrote:

Thank you that works, but I ended up using this method instead.

=SUMPRODUCT(('Real Monitor'!B$7:B$39=F40)*(ISNUMBER(SEARCH("S2",'Real
Monitor'!F$7:F$39))))

Jeff