Sumif with criteria across sheets with a weight multiplier

[MSO_user]

Dear Friends

I am here again and request humbly, if any of you have the time to solve my query.

Please find the attachment. The formula applied SUMIF formula across multiple sheets and then sum the item based on a criteria.

My query is simple, I want to get the sum of the quantities to be multiplied by a weight no.

I have also explained the resultant value which I need, in the attachment sheet as well.

Please review my sheet and help me in this regards. Any improvement in the previous code will be more than welcome.

Much Much Thanks for your support and time.....

 

[MSO_user]

Dear Friends

I got the solution.....on the an other forum.....and the link to the solution is below:

Link to the solution......

The name of the person is kvsrinivasamurthy

Thanks for your support and time.....

I love all the forums and the people who are putting their time and efforts in solving the problems of other peoples.......

 

[pecoflyer]

Also cross posted at https://www.mrexcel.com/board/thread...plier.1209616/

 

[vletm]

MSO_user

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[MSO_user]

Dear vletm

I really apologize for my bad behavior....I am really sorry....

I also have requested an apology on other forums as well...

For the Rules Noted....

I shall mention and request for cross posting.....Also I (have already and) will share the solutions to my problem, across the forums.

Much thanks for your guidance.....

Thanks

 

[MSO_user]

Dear Fellows

It took me some time....but I have understood the issues....and again thanks to ...kvsrinivasamurthy .....otherwise I would at zero....

There was an issue with the original solution....when we delete the items in the sheet list, then the we get errors in the formulae, in order to resolve the issue, we have to update the formulae. And the formulae starts working correctly...

I order to avoid changes in the original formulae...A unique list with zero weights should be added... so that when we modify the sheet list....this way we do not get errors.....

I am posting the original file (ending with (1)) and...posting the updated solution file (ending with (3))

I hope that this work shall help others as well....since it has helped me alot......indeed a lot....

Also I shall be sharing this solution with other forum members as well...

Much thanks and Much prayer for all of you guys.......

 

[Peter Bartholomew]

This is an initial experiment to see what the emerging array shaping functions available within 365 Insider beta can do with such problems. As I have it, the solution is not pretty!

= LET(
      sheet,         QUOTIENT(SEQUENCE(48,,8),8),
      shtWt,         SUMIFS(Weights, Sheets, UNIQUE(Sheets)),
      listItems,     TOCOL(Items),
      listQty,       TOCOL(Qty),
      filteredSheet, FILTER(sheet, listItems<>""),
      filteredItems, FILTER(listItems,listItems<>""),
      filteredQty,   FILTER(listQty,listItems<>""),
      distinctItems, UNIQUE(filteredItems),
      itemWt,        INDEX(shtWt, filteredSheet),
      quantity,      MAP(distinctItems,
        LAMBDA(item, SUM(IF(filteredItems=item, filteredQty*itemWt)))
      ),
      HSTACK(distinctItems, quantity)
  )

The formula uses the ability of the TOCOL function to reduce a 3D range to a single column. It first generates a column of sheet indices to provide a sheet number, and hence a weighting, for each item. It then filters out the blank items from each, the list of items, the list of quantities and the list of sheet numbers. UNIQUE is used to prepare a list of distinct items and then the MAP function runs through the list, summing the weighted quantities. The formula returns an array containing both the distinct items and the weighted quantity.

There might have been benefit in breaking the calculation down into smaller, more digestible functions but that would be a task for another day.

 

[MSO_user]

Much Thanks
Peter Bartholomew


For your time and efforts.....Thanks

 

[MSO_user]

Please Admin......The solution is available......Please consider it solved....