ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   What's Wrong With This Formula? (https://www.excelbanter.com/excel-discussion-misc-queries/218709-whats-wrong-formula.html)

Excel Nut
What's Wrong With This Formula?
 

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
What's Wrong With This Formula?
 
=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

Excel Nut
What's Wrong With This Formula?
 
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.

Pecoflyer[_93_]
What's Wrong With This Formula?
 

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

Excel Nut
What's Wrong With This Formula?
 
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.

Excel Nut
What's Wrong With This Formula?
 
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.

Pete_UK
What's Wrong With This Formula?
 
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

Excel Nut
What's Wrong With This Formula?
 
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

Excel Nut
What's Wrong With This Formula?
 
On Feb 2, 1:21*pm, Pete_UK wrote:
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- Hide quoted text -

- Show quoted text -

Sorry. I didn't notice that he had suggested a new formula. I thought
he had just put my original formula above hhis explanation of why it
wasn't working.

Yes, I have since tried Dave's formula and it works fine, except that
I had to modify it to refer just to the cells containing data. I had
originally allowed for an extra 1000 or so rows below my data to
accommodate new data to be added in the future. However, when I
include a reference to these blank cells in my SUMPRODUCT formula, I
get a #VALUE! error.

Any way around that?

Excel Nut

Thanks Dave and Pete.

xlmate
What's Wrong With This Formula?
 
Hi

try the formula provided by Dave, Adjust the range to suit yours and the
row numbers to your dataset as the recalculation would take some time
if you referenced all the rows in your spreadsheet

Post back if its don't work citing the error you receive and the formula you
use.

HTH

Pls provide your feedback

--
Pls click on the Yes button below if this posting is helpful.

Thank You

cheers, francis









"Excel Nut" wrote:

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

Excel Nut
What's Wrong With This Formula?
 
On Feb 2, 2:43*pm, xlmate wrote:
Hi

try the formula provided by Dave, Adjust the range to suit yours and the
row numbers to your dataset as the recalculation would take some time
if you referenced all the rows in your spreadsheet

Post back if its don't work citing the error you receive and the formula you
use.

HTH

Pls provide your feedback

--
Pls click on the Yes button below if this posting is helpful.

Thank You

cheers, francis



"Excel Nut" wrote:
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)*&allowsfile
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...460-Hidequoted 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- Hide quoted text -

- Show quoted text -

=SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000))

I have adjusted the formula so that it doesn't refer to ALL rows.
Currently my data goes down only to row 1541 but I need to reference
some extra rows below my current data to allow for additional data to
be added in the future. But when the SUMPRODUCT formula refers to
these empty rows it returns a #VALUE! error.

Any way around that?

Pete_UK
What's Wrong With This Formula?
 
I've adjusted the range to 2000, and included a further check:

=SUMPRODUCT((C11:C2000<"")*(LEFT(C11:C2000,1)="I" )*(F11:F2000=F10)*
(E11:E2000))

Just check that you have true blanks in column E.

Hope this helps.

Pete

On Feb 2, 5:35*pm, Excel Nut wrote:
On Feb 2, 1:21*pm, Pete_UK wrote:





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- Hide quoted text -

- Show quoted text -

Sorry. I didn't notice that he had suggested a new formula. I thought
he had just put my original formula above hhis explanation of why it
wasn't working.

Yes, I have since tried Dave's formula and it works fine, except that
I had to modify it to refer just to the cells containing data. I had
originally allowed for an extra 1000 or so rows below my data to
accommodate new data to be added in the future. However, when I
include a reference to these blank cells in my SUMPRODUCT formula, I
get a #VALUE! error.

Any way around that?

Excel Nut

Thanks Dave and Pete.- Hide quoted text -

- Show quoted text -

Pecoflyer[_94_]
What's Wrong With This Formula?
 

Pete_UK;210010 Wrote:
I've adjusted the range to 2000, and included a further check:

=SUMPRODUCT((C11:C2000<"")*(LEFT(C11:C2000,1)="I" )*(F11:F2000=F10)*
(E11:E2000))

Just check that you have true blanks in column E.

Hope this helps.

Pete

On Feb 2, 5:35*pm, Excel Nut wrote:
On Feb 2, 1:21*pm, Pete_UK wrote:





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- Hide quoted text -

- Show quoted text -

Sorry. I didn't notice that he had suggested a new formula. I
thought
he had just put my original formula above hhis explanation of why it
wasn't working.

Yes, I have since tried Dave's formula and it works fine, except
that
I had to modify it to refer just to the cells containing data. I had
originally allowed for an extra 1000 or so rows below my data to
accommodate new data to be added in the future. However, when I
include a reference to these blank cells in my SUMPRODUCT formula, I
get a #VALUE! error.

Any way around that?

Excel Nut

Thanks Dave and Pete.- Hide quoted text -

- Show quoted text -

Instead of using checks for extra data, etc... why don't you use
'Dynamic orranges' (http://www.contextures.com/xlNames01.html#Dynamic)


--
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

xlmate
What's Wrong With This Formula?
 
try removing the wildcard like this

=SUMPRODUCT((LEFT(C3:$C$60,1)="I")*(F3:$F$60=F2)*( E3:$E$60))

it should do what you want and if not pls send me your workbook
pls take a moment to click on the Yes button as your feedback to this post.

thanks


--
pls click on the Yes button below if this posting is helpful.

Thank You

cheers, francis



"Excel Nut" wrote:

On Feb 2, 2:43 pm, xlmate wrote:
Hi

try the formula provided by Dave, Adjust the range to suit yours and the
row numbers to your dataset as the recalculation would take some time
if you referenced all the rows in your spreadsheet

Post back if its don't work citing the error you receive and the formula you
use.

HTH

Pls provide your feedback

--
Pls click on the Yes button below if this posting is helpful.

Thank You

cheers, francis



"Excel Nut" wrote:
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)*&allowsfile
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...460-Hidequoted 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- Hide quoted text -

- Show quoted text -

=SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000))

I have adjusted the formula so that it doesn't refer to ALL rows.
Currently my data goes down only to row 1541 but I need to reference
some extra rows below my current data to allow for additional data to
be added in the future. But when the SUMPRODUCT formula refers to
these empty rows it returns a #VALUE! error.

Any way around that?

Dave Peterson
What's Wrong With This Formula?
 
First, you still can't use wild cards this way:

=SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000))

Second...

Do you have any #value! errors in any of those ranges? If so, clean up those
errors.

Do you have any non-numeric data in E3:E3000?

If you have text in column E, then the syntax you used (multiplying the
true/falses by text) will fail.

You could try:

=SUMPRODUCT(--(LEFT(C11:$C$65536,1)="I"),--(F3:$F$3000=F2),(E3:$E$3000))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Excel Nut wrote:

<<snipped
- Show quoted text -

=SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000))

I have adjusted the formula so that it doesn't refer to ALL rows.
Currently my data goes down only to row 1541 but I need to reference
some extra rows below my current data to allow for additional data to
be added in the future. But when the SUMPRODUCT formula refers to
these empty rows it returns a #VALUE! error.

Any way around that?

--

Dave Peterson


All times are GMT +1. The time now is 05:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com