Forum Discussion

cdiaa's avatar
cdiaa
Copper Contributor
Mar 06, 2020
Solved

How do I find the sum of cells that are either one number or the other?

I have a spreadsheet with 2500 rows of 10 randomly generated numbers (either 1, -1, 10, -10, 100 or -100). For every row, I want 3 functions. The first would calculate the absolute value of the sum of all the 1s and -1s. The second would do the same with the 10s and -10s and then divide by 10, and the third would do the same for the 100s and -100s and then divide by 100.

 

I'm very new to Excel so I don't know if there's an easy way to do this that I'm just completely missing, so please help me if you can. Thank you.

excel
Formulas and Functions

5 Replies

  • cdiaa's avatar
    cdiaa
    Copper Contributor
    Mar 06, 2020

    Haytham Amairah Twifoo Thank you, but this wasn't what I was looking for.

     

    I meant that I want something like this:

     

    -1 1 -1 1 1 would return the answer of 1 because -1 + 1 + (-1) + 1 + 1 = 1.

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Mar 06, 2020

    cdiaa 

    Your requirement is equivalent to the count of specific absolute numbers. Thus, the formula for the count of absolute 1's is: 

    =SUMPRODUCT(--(ABS($A1:$J1)=1))

     

    For 10's, the formula will be: 

    =SUMPRODUCT(--(ABS($A1:$J1)=10))

     

    For 100's, the formula will be: 

    =SUMPRODUCT(--(ABS($A1:$J1)=100))

     

    See the results of the foregoing formulas in the attached file.

Resources