Forum Discussion
Excel formula required
Try the following:
=SUM(MMULT(--($B$3:$F$12=$I3),TRANSPOSE($B$2:$F$2)))
It would help if you gave more info about the setup. It appears the AI values have no factor in the sum and if the same name is in both line it only counts 1x. There may be a fancy PIVOTBY or something that might work more elegantly but following (old school) formula seems to work (see attached):
=SUMPRODUCT(IFERROR(($B$2:$F$7>=100)*$B$2:$F$7,0)*((($B$3:$F$8=I3)+($B$5:$F$10=I3))>0))basically the first 1/2 is to get either the value or 0 based on a) it being a number and that number is >=100
the second 1/2 get either 0 or 1 based on if either the next row or the 3rd row has the name of interest
the sum of those products then gives you the answer.
Hi,
I have made a few changes to the file. The volume row is now appearing at the top while the individual items have extended downwards. Would you please assist do a summation of the volume per item ?
Re:
Items Volume totals
Star 700How do you get 700 from A2:F26?
sorry.it was a wrong attachment, I have uploaded a new file.
sorry I can't help because I can't make sense of what you want/need. Last time I came up with a scenario where ignoring the AI and # of occurences in the same group I could make the table fit your expected results. In this new file I can't figure out how to make it work so I can't create a formula. You will need to explain how to add the items in the table to get the 'expected results'
sorry, I attached the file and did not change the totals from the previous version. I have edited and attached the file again and hopefully this one makes sense.
Thank you.
Let me do further tests on my workbook and see if I am getting desired results.
Sorry, I forgot to mention that I was actually looking for this "old-school" formula that can work with excel 2019,2021 and not just 365.
