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
- [libreoffice-users] IF doesn't respect AND? · Hylton Conacher (ZR1HPC)
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.