SUMPRODUCT - Works with Three Criteria, Not Two - Why?

  • Thread starter Thread starter David Breingan
  • Start date Start date
D

David Breingan

Hi I've the following which is causing me problems:
Source Turnaround (Hours)
Column B J

XB 2.00
XB 16.7
XB 3.4

The following array formula will work perfectly when I
have three criteria:

=SUMPRODUCT(($B$2:$B$99="XB")*($J$2:$J$99>2.01)*
($J$2:$J$99<=4))

BUT Not with the following

=SUMPRODUCT(($B$2:$B$99="XB")*($J$2:$J$99>=0.1)*
($J$2:$J$99<=2))

There are cells containing 2.00 but this is not picked
up!!

Any help or Pointers appreciated, I can't understand why.

David
 
Hi
first: This formulas don't has to be array entered :-)

The formulas look o.k. Are you sure the alue is exact 2.00
(or could it be that this is a formula result and only 2
decimals are displayed?
so does for example the following work:
=SUMPRODUCT(($B$2:$B$99="XB")*($J$2:$J$99>=0.1)*
($J$2:$J$99<=2.01))
 
Hi David,

From what I can see, your formula should work. It's possible that the
cells containing 2.00 may actually be formatted as text. Try this:

Select a blank cell
Edit > Copy
Select the cell/range containing your number(s)
Edit > Paste Special > Add
Click Ok

Hope this helps!
 
Back
Top