SUMIF works only untill year 2017

Discuss the spreadsheet application
Post Reply
truohol
Posts: 27
Joined: Tue Oct 09, 2012 4:10 pm

SUMIF works only untill year 2017

Post by truohol »

Hello,

I have a following formula in B column: =SUMIF(Income.$D$3:$D$1893;$A29;Income.AF$3:AF$1893).

In the Income column d are years 2011-2018 and AF column is numbers, euros. A columns in the "Summary sheet" (the one where the function is entered) has years 2011-2018 and B column should get the function calculation.

So this function should find the years from Income sheet and compare them with year stated in "A29". If a match is found, it should sum them all together. This function has worked for the years 2011 to 2017, but now the 2018 year is not anymore calculating even though there is income already.

However if I replace the A29 in the forumula with "2018", the function works. But this was not needed earlier with the years 2011-2017, so what could be the cause for this and how to fix it? Any ideas?

Cheers, Tomas
Open Office 3.4.1 Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUMIF works only untill year 2017

Post by Villeroy »

The text "2018" is not the same as the number 2018
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
truohol
Posts: 27
Joined: Tue Oct 09, 2012 4:10 pm

Re: SUMIF works only untill year 2017

Post by truohol »

Hello.

I lnow, but why is was working just fine eith 2011,2012...2017, but not anymore 2018? As numvers, not text.


Tomas
Open Office 3.4.1 Windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUMIF works only untill year 2017

Post by Lupp »

Was there sorcery?
Did someone covertly set the cells you entered 2018 in on the 'Income' sheet to 'Numbers' format code '@'?
Did you inadvertently enter 2018 as a text into cell A29?
Have the income rows for 2018 higher row numbers than 1893?

The SUMIF function does not invoke a filter excluding 2018.
My first choice is "text in some rows of 'Income'".
In fact SUMIF would work with automatic type conversion if there isn't something like prefixed or trailing spaces.

Check every detail concerning the types -and:
truohol wrote:..., but now the 2018 year is not anymore calculating even though there is income already.
You did not tell us yet in what way exactly the formula told you that it wasn't anymore calculating.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUMIF works only untill year 2017

Post by robleyd »

Perhaps you could upload a sample file showing the issue you are having.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply