sumproduct w/horizontal range not working

  • Thread starter Thread starter dcd123
  • Start date Start date
D

dcd123

I am trying to run sumproduct with two criteria, both with a horizontal
range. It does not seem to be working and is returnig a zero count.
Here is what my formula looks like:

=SUMPRODUCT((E1:AS1="*Discussed*")*(E2:AS2="YES"))

The criteria for E1:AS1 (Discussed) is one of several words.

Any help would be greatly appreciated.

Thanks!
 
Try...

=SUMPRODUCT(ISNUMBER(SEARCH("Discussed",E1:AS1))*(E2:AS2="Yes"))

Hope this helps!
 
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Because SEARCH returns a #VALUE error if the text is not found. Testing this
for > 0 still returns #VALUE, whereas testing it for a number, ISNUMBER,
will return FALSE, which is coerced correctly to a number.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
dcd123 said:
Thank you. It did work! Can you explain why the ISNUMBER and SEARC
made the difference?
Click to expand...

SUMPRODUCT doesn't accept wildcards. ISNUMBER/SEARCH is a way o
achieving the same result. Let's assume that E1:I2 contains th
following data...


Code
-------------------
To_be_discussed x y To_be_discussed z
Yes No No Yes Ye
-------------------


...and that we have the following formula...


Code
-------------------
=SUMPRODUCT(ISNUMBER(SEARCH("Discussed",E1:I1))*(E2:I2="Yes")
-------------------


SEARCH("Discussed",E1:I1) returns the following array of values...


Code
-------------------
{7,#VALUE!,#VALUE!,7,#VALUE!}

Note that SEARCH returns a #VALUE! error when the text being searched is not found
-------------------


(ISNUMBER(SEARCH("Discussed",E1:AS1))) returns the following array o
values...


Code
-------------------
{TRUE,FALSE,FALSE,TRUE,FALSE
-------------------


(E2:AS2="Yes") returns the following array of values...


Code
-------------------
{TRUE,FALSE,FALSE,TRUE,TRUE
-------------------


SUMPRODUCT then multiplies the two arrays...


Code
-------------------
(ISNUMBER(SEARCH("Discussed",E1:I1))*(E2:I2="Yes"
-------------------


...and returns the folloiwng...


Code
-------------------
{1,0,0,1,0
-------------------


...which is summed, and returns 2. Note that numerical equivalent o
TRUE and FALSE is 1 and 0, respectively.

Hope this helps
 
Back
Top