In Excel, can you create an If Then formula that mathematically affects the result of a different cell?

Question

Example:  I have 42 answers in a survey.  Every answer is either "a" or "b".

If Answer 1 is "a", then I want cell A45 to add 1 to its total.  If Answer 1 is "b", then instead I want cell A46 to add 1 to its total.

If Answer 2 is "a", then I want cell A47 to add 1 to its total.  If Answer 2 is "b", then instead I want cell A49 to add 1 to its total.

If Answer 3 is "a", then I want cell A49 to add 1 to its total.  If Answer 3 is "b", then instead I want cell A45 to add 1 to its total.

So by the end of the 42 answers, there are going to be a number of cells (7 to be exact) which have a specific count of how many times an If Then statement has added 1 to that cell.

Is this possible?  In summary, I want a formula that says this:  "If cell A1 = "a" Then cell A45 = A45+1".  I just don't know the syntax or if this is even possible in Excel.

If it's not possible, does anyone have any other ideas?

Anonymous · Accepted Answer

You can't use a formula to change the value in a different cell. Did you consider a macro?

I get that you couldn't use a simple COUNTIF in each of the result cells, because for example the one in A45 wants to count an 'a' in Q1, and a 'b' in Q3, while ignoring 'a' and 'b' from most other questions. It makes the range too complex. If someone was forcing me to use your exact format, I would do the following.

After each answer cell, is helper cell, which can be hidden. This helper cell will contain formula that makes it show a, b, c, d, e, f, or g, depending on which of the seven result counting cells, we'll say A45 - A51, that question corresponds to.
So we'll say the answer for question 1 is in cell A1, and the helper cell is B1. B1 will say =IF(A1="a","a","b")
(I'm assuming you've already put validation on the answer cell so ONLY an answer of a or b will be accepted)
Then likewise, the answer for question 2 is in cell A2, so the helper cell, B2 says =IF(A2="a","c","d")
In each of the result count cells, they will look at the B2 range for their corresponding letter, so A45 will say =COUNTIF(B:B,"a"), A46 will look for a count of "b", A47 will look for "c", etc.

Of course that's a lot of work because it is not a formula that can be copied down without needing to check and edit every row, but that's what happens because the 'a' and 'b' all have specific and differing meanings depending on question.

pdq · Answer

Someone in another forum told me I can use the COUNTIFS function to do what I need.

Example:  =COUNTIFS(A1="yes",G1="yes",Q1="yes",W1="yes",AC1="yes",AM1="yes")

expletive_xom · Answer

your rules are fairly random (Example A45 on Q1 and Q3) so I would make helper columns that clearly define which row an "a" answer means and a "b" answer means.  i will put up a pic.

your helper columns would be columns B and C

- in B put the row number you want answer "a" associated with
- in C put the row number you want answer "b" associated with

then in A45, copy&paste this formula

=SUMPRODUCT(($A$2:$A$43="a")* ($B$2:$B$43=ROW(A45)))+ SUMPRODUCT(($A$2:$A$43="b")* ($C$2:$C$43=ROW(A45)))

drag the formula down to A51

-edit 
i put the pic up
so using your example with A49

I put 49 in C3 if Q2 answer is a B….in B4 if Q3 answer is an A…and C6 if Q5 is a B
since only 2 out of 3 are true, then row 49 counts 2.

you can start plugging numbers in to test.  you will notice i randomly put answers and numbers in the columns. like i changed you Answer to #2 to a B

i added column D because i added the header row, and that made row numbers deceptive...the row # didnt match the Q# any more.
speaking of deceptive, column b looks for A's and column C looks for B's...confusing?

hope that all makes sense.  let me know

? · Answer

you can use COUNTIF to count cells containing a specific value. however, if you want to count separate values, it would be best to "stack" the ones you want to sum together (in separate rows below each other) then SUM those afterwards.
https://exceljet.net/formula/count-cells-that-contain-specific-text

COUNTIFS might work better for you:
https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/

Trending News

In Excel, can you create an If Then formula that mathematically affects the result of a different cell?

4 Answers