Sum numeric values with different units

[justforreference123]

Hi Folks,

I wanted to have write a formula to sum up the item with specific unit and the expected set out is like that:

ITEM 1 10 PKT
ITEM 2 11 KG
ITEM 3 3 CTR
ITEM 4 5 KG
ITEM 5 30 KG
ITEM 6 5 PKT

Total PKT 15
Total KG 46
Total CTR 3

I tried with this formula:
=SUMPRODUCT(--(0&SUBSTITUTE(B3:B25, " PKT", "")))
but it shows ‘#value!”

Thank you

Ref.

[estige]

With unit in C and value in B:
=SUMIF(C2:C7,"PKT",B2:B7)

[shukla.ankur281190]

Hi Can you share your excel sheet??

[justforreference123]

Hi Can you share your excel sheet??

I am sorry for the format changed a bit

[FlameRetired]

Try array entering this formula in B10 and filling down and across to E12.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

This could be much simpler if your amounts and units were in separate columns. You could use the much more efficient SUMIF function. It would save you the trouble of splitting the data as I have done in the above ... and by the way this formula will not work if any amounts in rows 3:8 are greater than 99.

[justforreference123]

Thanks Dave !

You are amazing !
That worked just fine !!!

Regards,
Ref.

[FlameRetired]

You're welcome.

I had an afterthought.

This formula does not require Ctrl + Shift + Enter. Commit with simply Enter. It also overcomes the 99 limitation.

Please Login or Register  to view this content.

[justforreference123]

But I just encountered a problem when the cells empty.

[FlameRetired]

Try array entering this in B10. Fill down and across.

Please Login or Register  to view this content.

[justforreference123]

Dave,

You are Brilliant!
It works perfectly now !!

Regards,
Ref.

[FlameRetired]

Glad to hear it. Thanks for the feedback.

[Indi_Ra]

You can try this CSE formula:

In B10 then fill down and across.
=SUM(IFERROR(--SUBSTITUTE(B$3:B$7,RIGHT($A10,3),""),0))