Hi,
I am working with a spreadsheet that has a total of 5 named ranges and I need to use those named ranges to a series of SUMIFS calculations using different combinations of the ranges. One of the named ranges contains a series of names which I then use the UNIQUE formula to list each value only once. From there, I am trying to use the Names range with one of my other criteria ranges called Is_Judge which contains "Yes" and "No" values to do a summation of the values in the other named ranges. When I try to do the following sum using SUMIFS, =SUMIFS(Overall_Appearance,Names,I2,Is_Judge,"No"), it throws me a #VALUE! error. I know that the formula is breaking on the Names,I2 part because if I omit it and just do =SUMIFS(Overall_Appearane,Is_Judge,"No"), the formula works just fine. Oddly enough, using the Names Range and cell I2 doesn't throw any errors if I use say =COUNTIF(Names,I2), only when using SUMIF or SUMIFS. Does this have anything to do with me using the UNIQUE function in cell I2 and then trying to use that value in my sum formula? Do I need to modify the SUMIFS formula to change how it treats the value in I2? Should I be using SUMPRODUCT instead of SUMIFS? Any help is greatly appreciated.
