[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [libreoffice-users] [SOLVED] Re: Calc IF criteria statement help

Hi Remy,

On 2018/11/13 01:22, remygauthier wrote:
I went back to the original post and downloaded the file you had uploaded to
Google Drive. I made what I think were the correct mods and the results on
the Summary sheet are consistent with what has been entered. I have attached
the modified file, so you should be able to download it and have a look (you
will probably need to access the post via the nabble site -
nabble.documentfoundation.org/). Once we know what is wrong with the
changes, it should be easier to provide information.

I hope this helps.

I have reviewed your file on Nabble and after reviewing and inserting the formula into the current file, am pleased to announce that the issue is SOLVED! :)


Looking at the formula <=SUMIFS(Data.C$3:Data.C$1000,Data.$A$3:Data.$A$1000,CONCATENATE("=",$A3 ,"*"),Data.$F$3:Data.$F$1000,CONCATENATE("<>","Y"))>, I see how the Concatenate was used almost like an IF statement criteria, and could be stated as 'CONCATENATE("=",Summary.$A3 ,"*")'. However I do not understand the second CONCATENATE as I am looking for those with an "N" i.e. ones that have not been reconciled yet the SUMIFS yields the correct value?

Concatenate joins/amalgamates values from different cells or text i.e. as a Linux command $>cat filename would display the contents of the filename. My understanding is that a similar command of $>cat bee hive would result in the word beehive being displayed.

In the formula you state to SUM values in Data.c3:Data.c1000 if date value of column A equals a partial match of the actual Data column holding the date AND where the value is blank or Y, despite you only wanting to SUM the N values?

Basically the formula works and I am not complaining and very grateful but I would like to better understand the formula, so that I can use it in future.

I hope my explanation is sufficient.


P.S.: To ALL owners of a Debit or Credit Card, establish a spreadsheet using the above formula, enter your transactions on the Data tab, reconcile them according to your bank statement and see what value of goods you are getting for FREE as sometimes it pays to shop at certain retailers, even if it is only occasional.

To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] [SOLVED] Re: Calc IF criteria statement helpRemy Gauthier <remygauthier@yahoo.com>
[libreoffice-users] Calc IF criteria statement help"Hylton Conacher (ZR1HPC)" <hylton@conacher.co.za>
Re: [libreoffice-users] Calc IF criteria statement helpMichael H <cmahte@gmail.com>
Re: [libreoffice-users] Calc IF criteria statement helpRemy Gauthier <remygauthier@yahoo.com>
Re: [libreoffice-users] Calc IF criteria statement help"Hylton Conacher (ZR1HPC)" <hylton@conacher.co.za>
[libreoffice-users] Re: Calc IF criteria statement helpremygauthier <remygauthier@yahoo.com>
Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.