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