I need to compare the percentage value in column G with column F and then add up the toals of all cells that match the criteria.
The comparison is that the value in each cell in the range in column G is between 100% and 105% of the value in Column F.
I though if I insert another column E with the value of G + 105% for each cell I could then runthe folowing calcluation.
=SUMPRODUCT((G3:G117<=F3:F117)*(G3:G117>=E3:E117)*(A3:A117=1))
Hoever I got unexpected results, it returns just 1
The *(A3:A117=1)) part of the calculation is that it must have a 1 in that column to be included in the total.
A freind suiggested this as a solution:
=SUMPRODUCT((((G3:G117)/(F3:F117))>100%)*(((G3:G117)/(F3:F117))<=105%)*(A3:A17))
but all I get is a #VALUE!
I can attach the file if that helps
Bookmarks