Sumif where source values are assigned by "if" formula

  • Thread starter Thread starter irvine79
  • Start date Start date
I

irvine79

I am trying to sum the values in a column (Q20:Q300) based on an assigned
designation in another column (N20:N300).

The destination cell is Q11.

The criteria value is found in cell N11.

Values in cells Q20:Q300 are assigned via an "if" condition dependent on a
value assigned in column "O".

When I place the placed the following formula in my destination cell (Q11),
it returned a value of "0" which is incorrect. My assumption is that the
formula is recognizing the numeric value in column Q as text and is not
adding properly.

formula in Q11 ... =sumif(N20:N300,N11,Q20:Q300)


Please Help!

Thanks!
 
If your formulas in Q20:Q300 return numbers, it should work fine. But if your
formulas return text that look like numbers, you'll see 0.

So if your formulas look like:
=if(r20>7,"92","89")

make them return real numbers:
=if(r20>7,92,89)
 
=IF(O27="S",1,IF(O27="Asst",1,0))

Can be rewritten as:
=if(or(o27="s",o27="asst"),1,0)
or
=--(or(o27="s",o27="asst"))
or even
=--(or(o27={"s","asst"}))

The =or() function will return a true or false. The -- converts those boolean
values to a 1 or 0.

The formulas return the same values, so you don't have to change anything. But
I think that the others are easier to change if need be.
 
Back
Top