[libreoffice-users] IF doesn't respect AND?

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