What's Wrong With This Formula?

E

Excel Nut

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
 
D

Dave Peterson

=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$65536=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???).
 
E

Excel Nut

=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$65536=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???).








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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

Excel Nut

Excel Nut;209408 Wrote:







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/showthread.php?t=57460- Hide quoted text -

- Show quoted text -
Click to expand...

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

Excel Nut

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 -
Click to expand...

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

Pete_UK

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

=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$65536=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
 
E

Excel Nut

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 -
Click to expand...


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
 
E

Excel Nut

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

=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$65536=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








- Show quoted text -
Click to expand...

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

xlmate

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
 
E

Excel Nut

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











- Show quoted text -
Click to expand...

=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?
 
P

Pete_UK

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
 
X

xlmate

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
 
D

Dave Peterson

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct by date 4
Excel VBA 1
Need help with SUMPRODUCT Formula 10
counting multiple criteria using SUMPRODUCT and getting #VALUE err 1
Balance Sheet Formula 5
Sumproduct with OR 7
Formula Question 1
How do I reference a cell as a row number in a formula? 4

Top