On Feb 2, 10:32*am, Dave Peterson wrote:
=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536))

(You can't use wildcards like that.)

As an aside, if you need all those rows, fine. *But your formula will
recalculate much quicker if you limit the range to what you need (or a little
more for safety???).





Excel Nut wrote:

What's Wrong With This Formula?

=SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536))

- I'm using Excel 2000
- Column C contains text values
- Column F contains text values
- Column E contains numeric values
- My formula is in cell E10
- I want to sum cells in column E below the row containing this
formula using the following criteria...
- This formula is returning 0

Criteria:

1) cells in column C whose text values start with "I"
2) cells in column F whose text values equals the text value in cell
F10

Note:
- The purpose of the mixed references (i.e. relative/absolute) is
because I need to copy this formula down but it should just evaluate
cells below the row containing the formula.
- I've tried using F11 instead of F10 but I'm still returning 0

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Darn! Wildcards worked in my SUMIF formulas but this one I have two
criteria and that's why I used SUMPRODUCT.