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

[Stephen_Pisano]

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?

[#2]

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.

[#3]

@Stephen_Pisano 

You may try

=SUM(--P20:P45)

or

=SUMPRODUCT(P20:P45*1)

[#4]

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

[#5]

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

[#6]

Your second suggestion worked perfectly thank
you!

[#7]

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

[#8]

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

[#9]

@Stephen_Pisano

[#10]

@Stephen_Pisano 

You may use formulas like this

[#11]

@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???

[#12]

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

[#13]

@Stephen_Pisano , great, thank you for the updating us

[#14]

@Stephen_Pisano 

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)

[#15]

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 

[#16]

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!