Ask Your Question
0

How to check sum values?

asked 2017-09-11 09:31:01 +0100

Anticwar gravatar image

Hello! I have 200 columns with different Sum. For example: column A, Sum=841 B, Sum=850 C, Sum=841, and so on. I need to determine, how many, or which columns have equal value Sum? Thanks in advance!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-09-11 11:40:25 +0100

JohnSUN gravatar image

Counting the same summs is very easy, just use COUNTIF() like as

=COUNTIF(<range of row>;<Sum>)

For example =COUNTIF($A$1:$GR$1;841) or simple =COUNTIF(1:1;841) will count cells iin first row with value 841

Search columns with identical sums a bit more complicated and requires more complex formula. For example, it might be a formula similar to this

{=IFERROR(TEXTJOIN(",";1;IF(1:1=841;SUBSTITUTE(ADDRESS(1;COLUMN(1:1);4);"1";"");""));"")}

This formula show the column names separated by commas, where the first line contains the sum of 841. Or leave the cell empty if there is no value in any of the cells.

Important! Notice the braces in which the formula is enclosed! This means that this is an array formula and must be entered in a special way - the formula should be entered by pressing the key combination Ctrl + Shift + Enter or do as in this image ArrayFormula.png

Please not create new answer like as "Thx it work"!

Don't write "Thank you"

edit flag offensive delete link more

Comments

I'm not sure that I know how to do this, :-/

Anticwar gravatar imageAnticwar ( 2017-09-11 20:43:15 +0100 )edit

This is the same for both of us - I'm not sure that I correctly understood your problem by your description. :-) Now you can change the text of your question and add an example file with a row of sums and cells in which you want to get some results (you can write the details directly in this sheet). And I, in turn, will try to supplement my answer with a file, in which I will try to show the solution.

JohnSUN gravatar imageJohnSUN ( 2017-09-12 07:20:03 +0100 )edit

Thanks for your help! In fact, after a little research online, I think that you've provided a very good answer. I think that you understood exactly what my needs were. Now I know whom to ask in the future. Thanks again

Anticwar gravatar imageAnticwar ( 2017-09-12 09:35:13 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-11 09:31:01 +0100

Seen: 86 times

Last updated: Sep 11 '17