I wish to add a row of numbers dependent on whether another row of numbers is less than the value of a given cell. For example:
SUMIF (B2:B16, <A16, C2:C16)
I have tried this variant:
SUMIF (B2:B16, "<"&A16, C2:C16)
Non of the above works. I am using the newest version of numbers just released for the iMac and iPad. Is there an error in the release or am I just not getting the syntax right?
iPad (4th gen) Wi-Fi, iOS 7.0.3
Hi Polymetis,
What you need is this:
SUMIF(B2:B16,"<"&A16)
The quotes surround the < operator.
SG
The variant tried is:
SUMIF (B2:B16, "<"&A16, C2:C16)
not
SUMIF (B2:B16, "<"&A16, C2:C16)
WHAT!?
My text is being modified. The varient tried is:
SUMIF (B2:B16, <A16, C2:C16) where<is being replaced with "<" and &A16 is being added after "<". I have not inserted any amp
Ok I give up! Apparrently the use of "" and & triggers somekind of error. Jesus!
Hi SG
Thanks - thats the variant I have tried. But it is not working correctly. The formular only sums all of the cells or non of the cells. I can't figure out what determines what.
Maybe it is an error in the software
Hi Polymetis,
Here's what works here:
SG
P,
It might be a good idea to post this issue in the iOS software discussion area. Folks there may be aware of iOS iWork issues we Mac users may not yet know about.
Jerry
Well the same does not work here:
I am using the Danish version, som sum.hvis is sumif and the danish version uses ; instead of ,. But otherwise it should be the same.
It must be an error in the software.
Hi Polymetis,
In which cell is your formula?
Cell B16 contains
=SUMIF(B2:B15,"<"&A15)
Regards,
Ian.
Hi Polymetis,
Maybe a Danish localization/regional settings problem. Very strange. Definitely works here, both on Mac and iPad.
@Jerry, the formulas in iOS (as opposed to the interface) work exactly the same as in Numbers 3.0 as far as I can tell. Most of the formula expertise is here in this discussion area so I think Polymetis came to the right place as he seems to be using V 3.0 too.
SG
Hi Jerrold
I also experience the problem on the newest version of Numbers on my Macbook Air. I have just reported the problem.
David
Hi Ian
The formular is in C7. So this should be working,
David
@SG
I agree!
And there's one other possible cause of strange behavior of sums. Just a stab in the dark, though, and may not apply here.
Numbers 3.0 seems to screw up dates in some non-English settings. So if any one cell, anywhere, in the sum range happened to have been interpreted by Numbers 3.0 to have been a date, even if you catch that and reformat the cell to a number, the sum will not work thereafter. The only fix seems to be to "delete" the cell and reinput the number. Then the sum works again.
May not be applicable in this case, but keep on the lookout for dates creeping in where you don't mean to have dates.
SG
Using SUMIF less than value of a cell
