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.