SumIf - create subtotals based on criteria.

[RITCHI]

Hi

=SUMIF(A6:A2000,"(left(a6:a2000,4))=(left($K$14,4))",F6:F2000)
I want to create a sub total of all values in column F, at certain
subtotal cells in column F where the the first 4 (or other to be
set)characters of a code in cells a6 to a2000 match the first 4
characters in cell$K$14 (or other cell to be set).

The above doesn't seem to work, is it possible? am I missing something?
Any help would be greatly appreciated.

Ritchi

 

[Pete_UK]

Try this array* formula:

=SUM(IF(LEFT(A$6:A$2000,4)=LEFT($K$14,4),F$6:F$2000,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you should use CTRL-SHIFT-ENTER (CSE) instead of
just ENTER. If you do this correctly then Excel will wrap the formula
in curly braces { } when viewed in the formula bar - you should not
type these yourself.

Instead of explicitly including the 4 in the LEFT functions, you could
put the value in a cell, eg $K$13, and use this cell reference in the
formula so you can easily change the value.

A SUMPRODUCT formula could also be used.

Hope this helps.

Pete

 

[Bob Phillips]

=SUMPRODUCT(--(LEFT(A6:A2000,4)=LEFT($K$14,4)),F6:F2000)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[RITCHI]

Thanks; this pointed me in the right direction.
I've still ended up with a circular reference as the cell which I want
the total placing in is within the column that is being conditionally
summed. I've overcome this by using the next cell to the right for
totals; but is there a way to have this formual in a cell in the column
that is being summed without encountering the circular reference
problem?

Ritchi

 

[Pete_UK]

You can put the formula in the same column, as long as it is outside
the range that you are summing. Assuming you want it near the top of
your sheet, you have cells F1 to F5 where it could go. Alternatively,
if they are currently being used, insert a new row above 6 and put it
there.

Hope this helps.

Pete