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

=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

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.

Excel Nut;209408 Wrote:
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.

Why do you need the wildcard ?


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=57460

On Feb 2, 11:52*am, Pecoflyer
wrote:
Excel Nut;209408 Wrote:





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.

Why do you need the wildcard ?

--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)*& allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=57460- Hide quoted text -

- Show quoted text -

Here's a sample of my data (if you want to copy and parse it)...

Row|Column C|Column D|Column E|Column F
Row 1|J0000000042900|JAN|8.64|ALLOC FAX
Row 2|I0000000299000|JAN|18.94|ALLOC FAX
Row 3|I3001195880030|JAN|1.25|ALLOC FAX
Row 4|I3001310880030|JAN|17.69|ALLOC FAX
Row 5|J0101170880030|JAN|2.86|ALLOC FAX
Row 6|J0101175880030|JAN|0.11|ALLOC FAX
Row 7|J0101180880030|JAN|5.67|ALLOC FAX
Row 8|J0000000042900|JAN|30.29|ALLOC PHONE
Row 9|I0000000299000|JAN|28.35|ALLOC PHONE
Row 10|I3001195880040|JAN|0.25|ALLOC PHONE
Row 11|I3001195880040|JAN|1.39|ALLOC PHONE
Row 12|I3001196880040|JAN|2.97|ALLOC PHONE
Row 13|I3001210880040|JAN|4.99|ALLOC PHONE
Row 14|I3001310880040|JAN|18.75|ALLOC PHONE
Row 15|J0101170880040|JAN|23.65|ALLOC PHONE
Row 16|J0101175880040|JAN|2.84|ALLOC PHONE
Row 17|J0101180880040|JAN|1.85|ALLOC PHONE
Row 18|J0101185880040|JAN|1.95|ALLOC PHONE

I want a formula for cell E1 that will evaluate all rows below the
formula row that have values in Column C starting wiht "I" and have
values in column F that match the value in F1. I want to be able to
copy the same formula to E9 and 58 other cells further down,

This is the formula I tried but Dave said I cannot use wildcards.
=SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000))

I know I can use a SUMIF formula to sum all column C values starting
with "I".

=SUMIF(C1:C7,"I*",E1:E7)

But I also need to match values in column F with the value in column F
on the same row as the formula. If I could get this to work, I could
copy the same formula down for all sixty I0000000299000 totals without
needing to change the cell references, otherwise I will need to use 60
SUMIF formulas with different references for each one.

Thanks for looking at this.

On Feb 2, 1:11*pm, Excel Nut wrote:
On Feb 2, 11:52*am, Pecoflyer
wrote:





Excel Nut;209408 Wrote:

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.

Why do you need the wildcard ?

--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)*&allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:http://www.thecodecage.com/forumz/sh...p?t=57460-Hide quoted text -

- Show quoted text -

Here's a sample of my data (if you want to copy and parse it)...

Row|Column C|Column D|Column E|Column F
Row 1|J0000000042900|JAN|8.64|ALLOC FAX
Row 2|I0000000299000|JAN|18.94|ALLOC FAX
Row 3|I3001195880030|JAN|1.25|ALLOC FAX
Row 4|I3001310880030|JAN|17.69|ALLOC FAX
Row 5|J0101170880030|JAN|2.86|ALLOC FAX
Row 6|J0101175880030|JAN|0.11|ALLOC FAX
Row 7|J0101180880030|JAN|5.67|ALLOC FAX
Row 8|J0000000042900|JAN|30.29|ALLOC PHONE
Row 9|I0000000299000|JAN|28.35|ALLOC PHONE
Row 10|I3001195880040|JAN|0.25|ALLOC PHONE
Row 11|I3001195880040|JAN|1.39|ALLOC PHONE
Row 12|I3001196880040|JAN|2.97|ALLOC PHONE
Row 13|I3001210880040|JAN|4.99|ALLOC PHONE
Row 14|I3001310880040|JAN|18.75|ALLOC PHONE
Row 15|J0101170880040|JAN|23.65|ALLOC PHONE
Row 16|J0101175880040|JAN|2.84|ALLOC PHONE
Row 17|J0101180880040|JAN|1.85|ALLOC PHONE
Row 18|J0101185880040|JAN|1.95|ALLOC PHONE

I want a formula for cell E1 that will evaluate all rows below the
formula row that have values in Column C starting wiht "I" and have
values in column F that match the value in F1. I want to be able to
copy the same formula to E9 and 58 other cells further down,

This is the formula I tried but Dave said I cannot use wildcards.
=SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000))

I know I can use a SUMIF formula to sum all column C values starting
with "I".

=SUMIF(C1:C7,"I*",E1:E7)

But I also need to match values in column F with the value in column F
on the same row as the formula. If I could get this to work, I could
copy the same formula down for all sixty I0000000299000 totals without
needing to change the cell references, otherwise I will need to use 60
SUMIF formulas with different references for each one.

Thanks for looking at this.- Hide quoted text -

- Show quoted text -

WAIT ! ! messed up the references in that last post... Let me repost.

Did you actually try the formula that Dave gave you? i.e.:

=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E
$65536))

His comment meant that you don't use wildcards like that in SP -
instead you can use the LEFT function.

Pete

On Feb 2, 4:11*pm, Excel Nut wrote:

Here's a sample of my data (if you want to copy and parse it)...

Row|Column C|Column D|Column E|Column F
Row 1|J0000000042900|JAN|8.64|ALLOC FAX
Row 2|I0000000299000|JAN|18.94|ALLOC FAX
Row 3|I3001195880030|JAN|1.25|ALLOC FAX
Row 4|I3001310880030|JAN|17.69|ALLOC FAX
Row 5|J0101170880030|JAN|2.86|ALLOC FAX
Row 6|J0101175880030|JAN|0.11|ALLOC FAX
Row 7|J0101180880030|JAN|5.67|ALLOC FAX
Row 8|J0000000042900|JAN|30.29|ALLOC PHONE
Row 9|I0000000299000|JAN|28.35|ALLOC PHONE
Row 10|I3001195880040|JAN|0.25|ALLOC PHONE
Row 11|I3001195880040|JAN|1.39|ALLOC PHONE
Row 12|I3001196880040|JAN|2.97|ALLOC PHONE
Row 13|I3001210880040|JAN|4.99|ALLOC PHONE
Row 14|I3001310880040|JAN|18.75|ALLOC PHONE
Row 15|J0101170880040|JAN|23.65|ALLOC PHONE
Row 16|J0101175880040|JAN|2.84|ALLOC PHONE
Row 17|J0101180880040|JAN|1.85|ALLOC PHONE
Row 18|J0101185880040|JAN|1.95|ALLOC PHONE

I want a formula for cell E1 that will evaluate all rows below the
formula row that have values in Column C starting wiht "I" and have
values in column F that match the value in F1. I want to be able to
copy the same formula to E9 and 58 other cells further down,

This is the formula I tried but Dave said I cannot use wildcards.
=SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000))

I know I can use a SUMIF formula to sum all column C values starting
with "I".

=SUMIF(C1:C7,"I*",E1:E7)

But I also need to match values in column F with the value in column F
on the same row as the formula. If I could get this to work, I could
copy the same formula down for all sixty I0000000299000 totals without
needing to change the cell references, otherwise I will need to use 60
SUMIF formulas with different references for each one.

Thanks for looking at this

On Feb 2, 1:11*pm, Excel Nut wrote:
On Feb 2, 11:52*am, Pecoflyer
wrote:





Excel Nut;209408 Wrote:

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.

Why do you need the wildcard ?

--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)*&allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:http://www.thecodecage.com/forumz/sh...p?t=57460-Hide quoted text -

- Show quoted text -

Here's a sample of my data (if you want to copy and parse it)...

Row|Column C|Column D|Column E|Column F
Row 1|J0000000042900|JAN|8.64|ALLOC FAX
Row 2|I0000000299000|JAN|18.94|ALLOC FAX
Row 3|I3001195880030|JAN|1.25|ALLOC FAX
Row 4|I3001310880030|JAN|17.69|ALLOC FAX
Row 5|J0101170880030|JAN|2.86|ALLOC FAX
Row 6|J0101175880030|JAN|0.11|ALLOC FAX
Row 7|J0101180880030|JAN|5.67|ALLOC FAX
Row 8|J0000000042900|JAN|30.29|ALLOC PHONE
Row 9|I0000000299000|JAN|28.35|ALLOC PHONE
Row 10|I3001195880040|JAN|0.25|ALLOC PHONE
Row 11|I3001195880040|JAN|1.39|ALLOC PHONE
Row 12|I3001196880040|JAN|2.97|ALLOC PHONE
Row 13|I3001210880040|JAN|4.99|ALLOC PHONE
Row 14|I3001310880040|JAN|18.75|ALLOC PHONE
Row 15|J0101170880040|JAN|23.65|ALLOC PHONE
Row 16|J0101175880040|JAN|2.84|ALLOC PHONE
Row 17|J0101180880040|JAN|1.85|ALLOC PHONE
Row 18|J0101185880040|JAN|1.95|ALLOC PHONE

I want a formula for cell E1 that will evaluate all rows below the
formula row that have values in Column C starting wiht "I" and have
values in column F that match the value in F1. I want to be able to
copy the same formula to E9 and 58 other cells further down,

This is the formula I tried but Dave said I cannot use wildcards.
=SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000))

I know I can use a SUMIF formula to sum all column C values starting
with "I".

=SUMIF(C1:C7,"I*",E1:E7)

But I also need to match values in column F with the value in column F
on the same row as the formula. If I could get this to work, I could
copy the same formula down for all sixty I0000000299000 totals without
needing to change the cell references, otherwise I will need to use 60
SUMIF formulas with different references for each one.

Thanks for looking at this.- Hide quoted text -

- Show quoted text -


Here's a sample of my data (if you want to copy and parse it)...

Row|Column C|Column D|Column E|Column F
Row 1|J0000000042900|JAN|8.64|ALLOC FAX
Row 2|I0000000299000|JAN|18.94|ALLOC FAX
Row 3|I3001195880030|JAN|1.25|ALLOC FAX
Row 4|I3001310880030|JAN|17.69|ALLOC FAX
Row 5|J0101170880030|JAN|2.86|ALLOC FAX
Row 6|J0101175880030|JAN|0.11|ALLOC FAX
Row 7|J0101180880030|JAN|5.67|ALLOC FAX
Row 8|J0000000042900|JAN|30.29|ALLOC PHONE
Row 9|I0000000299000|JAN|28.35|ALLOC PHONE
Row 10|I3001195880040|JAN|0.25|ALLOC PHONE
Row 11|I3001195880040|JAN|1.39|ALLOC PHONE
Row 12|I3001196880040|JAN|2.97|ALLOC PHONE
Row 13|I3001210880040|JAN|4.99|ALLOC PHONE
Row 14|I3001310880040|JAN|18.75|ALLOC PHONE
Row 15|J0101170880040|JAN|23.65|ALLOC PHONE
Row 16|J0101175880040|JAN|2.84|ALLOC PHONE
Row 17|J0101180880040|JAN|1.85|ALLOC PHONE
Row 18|J0101185880040|JAN|1.95|ALLOC PHONE


I want a formula for cell E2 that will evaluate all rows below the
formula row that have values in Column C starting with "I" and have
values in column F that match the value in F2. I want to be able to
copy the same formula to E9 and 58 other cells further down,

This is the formula I tried but Dave said I cannot use wildcards.
=SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000))

I know I can use a SUMIF formula to sum all column C values starting
with "I".

=SUMIF(C3:C7,"I*",E3:E7)

But I also need to match values in column F with the value in column
F
on the same row as the formula. If I could get this to work, I could
copy the same formula down for all sixty I0000000299000 totals
without
needing to change the cell references, otherwise I will need to use
60
SUMIF formulas with different references for each one.

Thanks for looking at this.
Excel Nut