SumIF function

[joshdoc]

Is there a way to use a sumif formula (or something similar) if I a
trying to sum up totals of categories with the same prefix numbers bu
different suffix numbers?

For example:

400.001 5
400.002 6
400.2 3
401.00 5
401.23 5
403 15
405 4

The lines that start with "400" would be sum together, then the line
with "401" would be sum together, etc., etc

 

[Domenic]

Hi,

Taking your sample into consideration, try the following:

1) List your numbers in Column A, somewhere after your table, say A10
400
401
etc...

2) Enter this formula in B10, and copy it down as far as you need to:

=SUMPRODUCT(--(--LEFT($A$1:$A$7,3)=A10),($B$1:$B$7))

Hope this helps!

 

[CLR]

=SUMIF(A:A,"*400*",B:B)

or

Maybe check out Data > Subtotals..........


Vaya con Dios,
Chuck, CABGx3

 

[RagDyer]

Try this, where you can enter which prefix you wish to total, into C1:

=SUMPRODUCT((--LEFT(A1:A100,3)=C1)*B1:B100)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Is there a way to use a sumif formula (or something similar) if I am
trying to sum up totals of categories with the same prefix numbers but
different suffix numbers?

For example:

400.001 5
400.002 6
400.2 3
401.00 5
401.23 5
403 15
405 4

The lines that start with "400" would be sum together, then the lines
with "401" would be sum together, etc., etc.

 

[William]

Hi joshdoc

=SUMPRODUCT((INT(A1:$A7)=400)*(B1:B7))
or
=SUMPRODUCT((LEFT(A1:A7,3)="400")*(B1:B7))



--
XL2002
Regards

William

[email protected]

| Is there a way to use a sumif formula (or something similar) if I am
| trying to sum up totals of categories with the same prefix numbers but
| different suffix numbers?
|
| For example:
|
| 400.001 5
| 400.002 6
| 400.2 3
| 401.00 5
| 401.23 5
| 403 15
| 405 4
|
| The lines that start with "400" would be sum together, then the lines
| with "401" would be sum together, etc., etc.
|
|
| ---
|
|

 

[Aladin Akyurek]

Since it is the prefix that counts...

=SUMIF(A:A,"400*",B:B)

or

=SUMIF(A:A,E2&"0",B:B)

where E2 houses a prefix like 400.

=SUMIF(A:A,"*400*",B:B)

or

Maybe check out Data > Subtotals..........


Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thank you Aladin...........of course that's the way it should be.

Vaya con Dios,
Chuck, CABGx3

Since it is the prefix that counts...

=SUMIF(A:A,"400*",B:B)

or

=SUMIF(A:A,E2&"0",B:B)

where E2 houses a prefix like 400.