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.