Sumif/vlookup/left
 

I have the following spreadsheet:

Amount Work Order
25.65 AA-07020.0600
500.00 S-070200600.002
(904.81) AA-06020.0300.007
1000 08.5552.0000
585 07.526.589

I am trying to find using how to sum only the work orders that begin with an
AA and S.

Thanks,

Sumif/vlookup/left
 

One way

=SUM(SUMIF(B2:B6,{"AA-*","S-*"},A2:A6))

this assume there is a dash after the A and the S, if not just remove it

=SUM(SUMIF(B2:B6,{"AA*","S*"},A2:A6))


--
Regards,

Peo Sjoblom



"Gingit" wrote in message
...
I have the following spreadsheet:

Amount Work Order
25.65 AA-07020.0600
500.00 S-070200600.002
(904.81) AA-06020.0300.007
1000 08.5552.0000
585 07.526.589

I am trying to find using how to sum only the work orders that begin with
an
AA and S.

Thanks,

Sumif/vlookup/left
 

Try this:

=SUMPRODUCT(--((LEFT($B$1:$B$5,2)="AA")+(LEFT($B$1:$B$5,1)="S")) ,$A$1:$A$5)

HTH,
Elkar

"Gingit" wrote:

I have the following spreadsheet:

Amount Work Order
25.65 AA-07020.0600
500.00 S-070200600.002
(904.81) AA-06020.0300.007
1000 08.5552.0000
585 07.526.589

I am trying to find using how to sum only the work orders that begin with an
AA and S.

Thanks,

Sumif/vlookup/left
 

Try this:

=SUMPRODUCT((LEFT(B2:B6,2)={"AA","S-"})*(A2:A6))


"Gingit" wrote:

I have the following spreadsheet:

Amount Work Order
25.65 AA-07020.0600
500.00 S-070200600.002
(904.81) AA-06020.0300.007
1000 08.5552.0000
585 07.526.589

I am trying to find using how to sum only the work orders that begin with an
AA and S.

Thanks,

Sumif/vlookup/left
 

Thanks you guys are awesome.

Gingit

"Teethless mama" wrote:

Try this:

=SUMPRODUCT((LEFT(B2:B6,2)={"AA","S-"})*(A2:A6))


"Gingit" wrote:

I have the following spreadsheet:

Amount Work Order
25.65 AA-07020.0600
500.00 S-070200600.002
(904.81) AA-06020.0300.007
1000 08.5552.0000
585 07.526.589

I am trying to find using how to sum only the work orders that begin with an
AA and S.

Thanks,