Forum Discussion
Checking Ranges
I am trying to sum and check the value of ranges at the same time. I have three different ranges. Each range contains the same data sorted differently. I have one cell that I would like to have the sum of the data in. However, I would like it to only sum the data is range 1 = range 2 & = range 3. Can someone help with this?
- ryan_kCopper Contributor
You could you a command like the following.
=IF(AND(SUM(C:C)=SUM(D:D),SUM(C:C)=SUM(E:E)),SUM(C:C),"Not equal")
This will check is the sums for all three ranges are equal and then print the sum of one range if they are equal. One drawback is that this doesn't guarantee that each range is the same, only that the sums are equal. You could also use other tests such that checking if the sums, averages, standard deviations and products all are equal to be more safe. Further tests (such as length) can be added to the AND() statement. An equation with some of these checks is below.
=IF(AND(SUM(C:C)=SUM(D:D),SUM(C:C)=SUM(E:E),AVERAGE(C:C)=AVERAGE(D:D),AVERAGE(C:C)=AVERAGE(E:E),STDEV.P(C:C)=STDEV.P(D:D),STDEV.P(C:C)=STDEV.P(E:E)),SUM(C:C),"Not equal")
This is a long equation but it works for my test case.
Cheers,
Ryan