SUMIF Function

[Manos]

Dear all

I have to add and subtract the values of some cells.So that's the case
In the first column i have the codes of account which have in the first part
4 numbers underscore and some other figures. (1000_A1, 1000_A2, 1050_A3,
2000_A4).

So based on the first four numbers i want to add those that have 000 and
subtract those that include 50 (eg lets say that the third character to be 5
and the fourth characted to be zero).

So i need to add 1000_a1 + 1000_a2 + 2000_a4
and sub tract the 1050_a3

I thouhg to use one sumif to add those include 000 and another one to sumif
with 50.The problem is that i do not know how to set up the if match in SUMIF.

Any suggestions?

Thanks in advance
Manos

 

[PCLIVE]

Maybe one way:

=SUMPRODUCT(--(MID(A1:A4,2,3)="000")-(MID(A1:A4,3,2)="50"),LEFT(A1:A4,4)*1)

HTH,
Paul

 

[Sandy Mann]

Try:

=SUMPRODUCT(((ISNUMBER(FIND("000",A1:A4)))*B1:B4)-((ISNUMBER(FIND("50",A1:A4)))*B1:B4))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Manos]

Thank you very much