Match Part of text in a cell and "sum"

[whiZZfiZZ]

Hi everyone.

Great forum you have here, i have used quite a few tips when i hav
been lurking.

I was hoping someone might be able to help me with a problem im having
more to the point, i dont really know where to start.

In one work sheet i have two columns A and B. Column A contains accoun
numbers, column B contains their balance.

the account numbers look like this:
13.10.10
13.10.11
13.10.12
etc.. etc..

what i would like to do is, in another worksheet i want to be able t
add balances together (column B) where the account number starts wit
13.1

does anyone know how to do this ? any help would be greatl
appreciated

thank you.
:

 

[Peo Sjoblom]

Do the account numbers look like 13.10. with an ending period
and the values you want to sum are in your example 10, 11 and 12

=SUMIF(A2:A20,"13.10.",B2:B20)

fiddle with the 13.10. to fit your criteria.
Also if these are exported from some accounting program or similar the
values you want to sum might be text and the above formula will return 0,
if that's the case force them to be number by copying an empty cell,
select the values and do edit>paste special and select add.

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

This will do it

=SUMPRODUCT(--(LEFT(A1:A10,4)="13.1"),B1:B10)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[whiZZfiZZ]

Mate, you are a dead set bloody genius.

Thank you so much, and for the quick reply too!!!


I can understand the basics to how you did that, but what do the two -
do ???

"=SUMPRODUCT(--(LEFT)

 

[Bob Phillips]

The crux of the formula, (LEFT(A1:A10,4)="13.1"), is testing all of the data
for starting with 13.1, but the results are returned as an array of
True/False. This needs to be coerced to numeric values of 1/0 before they
are multiplied by your values in column B. The -- does this. It can equally
be achieved by 1*, or the N function, or even multiplying the first part of
the formula by the second (rather than using ,), but -- is fast becoming the
accepted practice in these groups, as it takes less processing, which could
be important in a large spreadsheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)