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?