 # Totalling a column that includes an ‘IF’ formula that produces a number

I have created a column of numbers, 0 or 1, that is produced with an IF command in each cell relating to an adjacent column of cells, eg O20 and so on. When I use a SUM formula eg =SUM(P20:P45) to total the ‘1s the outcome is alway 0. The only way I have discovered to total the column which counts the ‘1s’ is by =SUM(P20+P21 etc) which is time consuming and problematic when adding new rows. Any suggestions out there?
15 Replies

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

You might have this issue if the returned 1's are text rather than a number - does your formula resemble this:

=IF(true_value, "1", "0") - The speech marks denote that the results would be text and would not be summed.
=IF(true_value, 1, 0) - This version would return numbers as a result and can be summed.

If you can share a copy of your workbook we can take a look.

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

You may try

``=SUM(--P20:P45)``

or

``=SUMPRODUCT(P20:P45*1)``

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Thanks for your suggestion- I will give a a try ASAP

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Thanks for your suggestions- I will give them a try Asap

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Your second suggestion worked perfectly thank
you!

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Hi there, I tried =SUMPRODUCT(P20:P45*1) and it works perfectly. Thanks for your suggestions.

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Sergio, I have a new problem that you may be able to solve. I have a new column that relates to a set of individuals. When their scores increase from one test to another I insert a + in the column. When they decrease insert a -. I want to be able to create two formulas in two cells adjacent to the column of +s and -s, one that will give me the number of +s and one for the -s. Is this possible do you think? Steve

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

@Stephen_Pisano # Re: Totalling a column that includes an ‘IF’ formula that produces a number

You may use formulas like this # Re: Totalling a column that includes an ‘IF’ formula that produces a number

@Sergei Baklan Many thanks Sergie -that is very kind of you. I have attached a workbook that copied your suggestion but it is totalling zeros instead of the correct numbers???

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

@Stephen_Pisano Hi Sergei - please ignore my last message - I've corrected the formula and it works perfectly - thank you again!!!

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

@Stephen_Pisano , great, thank you for the updating us

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Good morning, Stephen

I have looked at several sites, and your previous help to others, but I am unable to solve my problem, and I was hoping you could help.

I have a working "if" statement but have not figured out how to get the resulting numeric place in another cell for eventual summation for a total.

My present statement starts with columns A, B (formula), F (cost) and I am trying to get a number value (cost) in C to be able to tabulate at the end of the column!

cell a5 which activates the formula with an "x" (otherwise nothing), my formula goes into b5 which returns the numerical value or 0       =IF(A5="x";1*F5;0)    I need to finish this statement but have not discovered how after several hours.

All the videos I watch give all kinds of "how to do different scenarios" but don't seem to address my specific need, albeit, I may have missed it.

I am 77 years old, and used to have more command of how to do various statements, but my previous knowledge seems to be lost to time! Your help would be greatly appreciated!

Dennis Galvan

214-460-7497 (text or call)

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Our online community's mission is to provide a platform for IT administrators, developers, MVPs, and Microsoft employees to interact. We want Tech Community to be a one-stop shop for product news, best practises, and thought leadership.@Stephen_Pisano

# Re: Totalling a column that includes an ‘IF’ formula that produces a number

Hi Dennis, I’m really sorry but I have no idea as to how to solve your problem. I’ve only used the forum to get help not offer advice as I’m an occasional user of this group. If you posted your problem openly to the forum rather than a direct message I’m sure you’ll find an answer!