Excel formula question: Add these numbers IF ...

Factual Questions
1

OK - I’m working on property taxes here at work, and I’ve got a conundrum. I’m pretty sure there’s a way to create this formula, but I can’t figure it out.

Column F shows the amount of taxes due. Column G is a Yes/No column - some of the appraisals are under appeal, some are not. I need to add the amounts due for the properties that are NOT currently under appeal.

I have done this manually by simply telling it to ADD and clicking on each cell, but I’ve got to have a formula, since the boss is going to want to see four different variations of the spread sheet.

So, great and brilliant Dopers, HELP!!

2

I’m not clear on what you want to do. What do you mean when you say “I need to add the amounts due for the properties that are NOT currently under appeal” - you need to add what? There is just one amount in Column H for each property, right?

I think what you mean is that: If there is a Yes in Cell G1, then copy the amount shown in F1 to H1; if there is a No in G1, then leave Column H1 blank.

Is that what you mean? Because the formula will be different if you are actually adding something up. Please advise.

3

Sigh. I meant: there is just one amount in Column F, right?

4

Have you tried the “sumif” function?

5

SUMIF is the way to go.

6

Assuming your totals are between Row 5 and 19, this should work:

=SUMIF(G5:G19,“no”,F5:F19)

I think it only works if the columns are right next to each other, but I might be wrong…

7

I tried this, but it didn’t work. I got 0 as my answer.

I was able to get help from my mother-in-law (of all people), and here’s what she did:

She added Column H. Column H was an IF/THEN - if G = N, then H = F; if G = Y, then H = 0. Then, she totalled at the bottom. It may not have been the fastest way to go, but it did indeed work.

Thanks for all of your suggestions! (You guys are FAST!)

8

That works too!

Are you using Y or N or “Yes” and “No”

Either way, the earlier formula should work, granted that what you put into quotes is what you are using in column G…

9

I would have done it like this:

In H1, type: =IF(G1=“yes”,F1,IF(G1=“no”,“0”))
Fun with formulas! :slight_smile:

10

If “yes” or “no” are the only possiblities, then the second IF statement is unnecessary. Also, the second IF statement is formatted incorrectly - it needs one more argument.

11

I got the same “0” result just now when I tried it. I used the formula helper, and I had the columns reversed. Make sure you have the range and the sum range in the correct order in te formula.

12

This does work and the columns do not need to be next to each other.

13

I think you are incorrect:


	
Row1	2,000	yes	2000         =IF(G1="yes",F1,IF(G1="no","0"))
Row2	3,000	yes	3000
Row3	1,500	no	      0
			
			
Row8	2,000	yes	2000          =IF(G8="yes",F8)
Row9	2,000	no	FALSE

If “yes” and “no” are the only possibilities and you don’t have the second statement, then “false” will appear if the answer is “no.” If you don’t mind “false” appearing, then you are correct. I for one do not like to see the word “false” when I really want to see a zero or a blank.

The formula does work. It does not need one more argument to do what I said it would do. Copy the above and try it. Naturally there could be another way to get the same result, but my formula is correct as written.

14

Sorry for the late response. What I meant was, replace the second “IF” statement with “0”. That gives you the same result without having to decode two “IF” statements.

15

You could also right that formula as:

=F1*(G1=“yes”)

16

Or you could even “write” it that way.