# SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help required)

1. ## SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help required)

Hi, I have exported accounts data in excel from Tally software. Now stocks of the items are in Cartons (boxes); please help me, how to convert them into KG because record of all other items is on KG only. Please see attached excel sheet.

I can get the sum of boxes for desired products but cannot convert them to KG in same formula. Currently I am using Vlookup for each product, maintaining separate sheet for each individual product, converting the boxes into units and then in KG. But number of sheets are too much in single work book.

If we can use SUMIF to get sum/count of few items from big data, then there must be a way to get it multiplied with respective units.

2. ## Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

Try this:

=SUMPRODUCT(Table1[Boxes],E\$4:E\$13,F\$4:F\$13)

3. ## Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

Thanks Ali for your quick response.
I tried this but sorry, its giving error. I have uploaded revised (more clear) sheet in my first post. Can you please check? Thanks

4. ## Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

I think this will do it:

=SUMPRODUCT(Table1[Boxes]*SUMIF(Table2[Filter Criteria],Table1[Product],Table2[KG/Box]))

5. ## Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

Wow.... You are awesome..... this worked like a charm.

Thanks AliGW. much appreciated.

6. ## Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1