Adding and counting cells containing formulas

Question

I'm trying to do two things. Using Excel 2010. I want to add the value of a range of cells whose value is derived from this formula: =IF(M8=1,"1",IF(M8=2,"0",IF(M8=3,"N/A"))). I've tried SUMIF formulas to only add the values in the range if =1, doesn't work. I've used a formula adding each cell individually (M8+M9+M10 etc), but if the value = N/A is doesn't work.

Separately, I want to count the number of cells with a value of 1 and 0 but not N/A. I'm stumped nothing works.

barry houdini · Accepted Answer · 2013-10-07 20:02:43Z

1

Don't use quotes around the numbers, try this version

=IF(M8=1,1,IF(M8=2,0,IF(M8=3,"N/A")))

Now you should be able to sum the results using SUM function because the results are numeric rather than text (and N/A will be ignored by SUM function)

...and to count 1s with COUNTIF, assuming range is N2:N100 use COUNTIF like

=COUNTIF(N2:N100,1)

do the same with zeroes

[of course counting the 1s is the same as summing the range because 1 is the only non-zero number that your formula produces]

edited Oct 7, 2013 at 20:02

answered Oct 7, 2013 at 19:53

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding and counting cells containing formulas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related