Using a wild card * with Sumproduct

  • Thread starter Thread starter jeffsmarketplace
  • Start date Start date
J

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
 
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
 
**************
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!
 
**************
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,
Click to expand...
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:
Click to expand...
This is the formula I am using
Click to expand...
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F$39="s1-
prd-std"))
Click to expand...
I would like to do this
Click to expand...
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))
Click to expand...
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"))
Click to expand...
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.
Click to expand...
regards,
Jeff
Click to expand...
Click to expand...

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
 
You are welcome and thanks for feeding back!

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


**************
Question-1:-
**************
I would like to do this
Click to expand...
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*('Real Monitor'!F$7:F
$39="s1*"))
Click to expand...
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.
Click to expand...
=SUMPRODUCT(('Real Monitor'!B$7:B$39=E31)*(ISNUMBER(SEARCH("S1",'Real
Monitor'!F$7:F$39))))
**************
Question-2:-
**************
Click to expand...

[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"))]
Click to expand...
Just mention the text inside the array notion that is {} like the
below will do the work which you are expecting to do.
Click to expand...
=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"}))
Click to expand...
Hope it's clear to you!
On Aug 27, 8:28 am, jeffsmarketplace <[email protected]>
wrote:
Click to expand...

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
Click to expand...
 
Back
Top