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.
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.