Forum Discussion
Excel formula required
Hi,
I am looking for a formula that would do a sum of the individual items on the workbook. They are not well arranged but the workbook is in use that way. I have provided a sample file with the expected results.
Try the following:
=SUM(MMULT(--($B$3:$F$12=$I3),TRANSPOSE($B$2:$F$2)))
9 Replies
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?
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.
