Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index


Hi,

I have a spreadsheet of two tabs with tab 1 named 'Data' and the other named 'Summary'. The purpose of this spreadsheet group is to record all credit card account transactions on the 'Data' sheet and then see what value or transaction slips have not bee submitted by suppliers i.e. the goods were acquired FREE.

'Data' tab
The data on the 'Data' tab consists of 5 columns labelled in Row 1 as Date,Description,Bank1,Bank2, Reconciled

Format of the date values are 8 digit yyyymmdd values. The Description field holds text, either bank 1 or bank2 hold a currency value to two decimal places.

'Data' Tab Explanation:
Purchases are recorded in the 'Data' tab with a 8 digit date value and a currency value in either bank1 or bank2. When each bank account is reconciled a Y is placed in the reconcile column showing that the corresponding transaction reflects on the bank statement.

'Summary' Tab:
The data on the summary tab consists of 5 columns labelled in row 1 as Date, Description, Bank1, Bank2, Total

The format of the Date value is a 6 digit value i.e. yyyymm. The Description is text and does not change. The values listed under Bank 1 or Bank2 are the SUM total of the values listed in the 'Data' sheet, for the particular month/6-digit date under a corresponding Bank1 or Bank2.

'Summary' Tab Explanation:
This includes 6 digit dates(YYYYMM) for each month with the same text Description for each. The value listed in the Bank1 column in the same row as the month concerned is a Sum of all the transaction values from the bank1 column on the 'Data' tab for the same month. The value listed in the Bank2 column in the same row as the month concerned is a Sum of all the transaction values from the 'Data' tab for the same month under the Bank2 column.

Both the 'Data' and 'Summary' sheets have the 2nd row used to Sum the values below just for interest.

The issue I am having identifying a formula that would Sum all the values from the 'Data' tab under Bank1 for a specific month shown in the 'Summary' sheet AND that have NOT been reconciled.

I have managed, after going through many, many formulae to find one that 'works' but a letter N needs to be placed in the reconciled column as opposed to just being blank, or meeting the criteria by having a Y in it.


that have not been reconciled and also only have the the first 6 digits of the 8 digit date matching the year and month on the 'Summary' sheet.

Consequently a formula that would Sum all the values from the 'Data' tab under Bank2 that have also NOT been reconciled and also match the 6 digit date on the 'Summary' tab eludes me.

I have Googled silly and tried so many formulae that I ask for help.

Here are some of the formulae I tried:


To search among the 8 digit dates in the 'Data' tab for a 6 digit date value I have in the 'Summary' tab, I am using:
INT($Data.$A$3:$Data.$A$1000/100)=Summary.$A3

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUMIF($Data.F$3:$Data.F$1000,$Data.F$3:$Data.F$100<>"Y",$Data.C$3:$Data.C$1000),0)

=SUMIF(SUMPRODUCT(INT($Data.$A$3:$Data.$A$1000/100)=$A4,$Data.C$3:$Data.C$1000) ,$Data.F3:$Data.F34<>"Y",$Data.C3:$Data.C1000)

=SUMIF($Data.F$3:$Data.F$1000,AND(INT($Data.A$3:$Data.A$1000)=A3,VLOOKUP($Data.F$3:$Data.F$1000<>"Y",$Data.A$3:$Data.D$1000,4,0)),$Data.D$3:$Data.D$1000)

=SUMIFS($Data.D3:$Data.D32,$Data.A$3:$Data.A$1000,INT($Data.$A$3:$Data.$A$1000/100)=$A4,$Data.F$3:$Data.F$1000,$Data.F$3:$Data.F$1000<>"Y")

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUM(SUMIF($Data.$C$3:$Data.$C998,"$Data.$F$3:$F1000<>Y"),SUMIF($Data.$D$3:$Data.$D998,"$Data.$F$3:$F1000<>Y")),0)

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A4,SUM(SUMIFS($Data.$C$3:$Data.$C999,$Data.$F$3:$Data.$F999,"<>*Y*"),SUMIFS($Data.$D$3:$Data.$D999,$Data.$F$3:$Data.$F999,"<>*Y*")),0)

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUMIF($Data.F$3:$Data.F$1000,VLOOKUP("Y",$Data.F$3:$Data.F$100,4,0),$Data.C$3:$Data.C$1000))

=IF(INT($Data.$A$3:$Data.$A$998/100)=$A3,SUM(SUMIF($Data.$C$3:$Data.$C998,"$Data.$F$3:$F1000<>Y"),SUMIF($Data.$D$3:$Data.$D998,"$Data.$F$3:$F1000<>Y")),0)

=VLOOKUP(INT($Data.$A$3:$Data.$A$998/100)=$A3,$Data.B3:F1000,3,1)

=SUMIFS($Data.C3:C50,$Data.A3:A50,INT($Data.$A$3:$Data.$A$1000/100)=$Summary.$A3,$Data.F3:F50,"N")

I am using the formula of

=IF(INT($Data.$A$3:$Data.$A$1000/100)=$A3,SUMIF($Data.F$3:F$1000,"N",$Data.C$3:C$1000),0)

This formula works but I would like to have a blank field i.e. "" instead of "N". Converting "N" to "" makes the formula inaccurate as it SUMS all months where the field is blank, not just the the date specified by '$A3' in the above formula.

Changing the formula to read: =IF(AND(INT($Data.$A$3:$Data.$A$1000/100)=$A3,$Data.F$3:F$1000=""),SUM($Data.D$3:D$1000),0) instead of: =IF(AND(INT($Data.$A$3:$Data.$A$1000/100)=$A3,$Data.F$3:F$1000="N"),SUM($Data.D$3:D$1000),0) also provides an incorrect value i.e. the rounding done seems to be ignored despite being a condition under AND?

You can view the two tabs here:
 <https://drive.google.com/open?id=1L_7MeE1vlg5Sr80TNzXVSqHblDliEyjt>

Comments appreciated.

Regards
Hylton

--
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

Context


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.