Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounts functions
Hi I have imported data (nominal ledger transactions) from Sage 50 and need
to query the results to total transactions for a particular nominal ledger code that fall between given dates. I have attempted to use sumif, but find i can only place 1 criteriea on it and sumproduct seems to dislike the date range |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounts functions
the criteria can be any boolean operation including a AND function
=sumfi(A1:a100,and(criteria1,criteria2,criteria3), B1:b100) "philn" wrote: Hi I have imported data (nominal ledger transactions) from Sage 50 and need to query the results to total transactions for a particular nominal ledger code that fall between given dates. I have attempted to use sumif, but find i can only place 1 criteriea on it and sumproduct seems to dislike the date range |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounts functions
Thanks for the rapid reply Joel!!
So that future transactions can be included i have a number of empty cells at the end of the ranges for my criteria, which i really need to leave in as the dat will be refreshed on a monthly basis, so unfortunately using this i get an empty cell reference, any idea how i may be able to overcome this issue? "Joel" wrote: the criteria can be any boolean operation including a AND function =sumfi(A1:a100,and(criteria1,criteria2,criteria3), B1:b100) "philn" wrote: Hi I have imported data (nominal ledger transactions) from Sage 50 and need to query the results to total transactions for a particular nominal ledger code that fall between given dates. I have attempted to use sumif, but find i can only place 1 criteriea on it and sumproduct seems to dislike the date range |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounts functions
sorry should also have noted that the results need to be displayed on a
seperate worksheet, as the imported data will be held in a hidden worksheet "Joel" wrote: the criteria can be any boolean operation including a AND function =sumfi(A1:a100,and(criteria1,criteria2,criteria3), B1:b100) "philn" wrote: Hi I have imported data (nominal ledger transactions) from Sage 50 and need to query the results to total transactions for a particular nominal ledger code that fall between given dates. I have attempted to use sumif, but find i can only place 1 criteriea on it and sumproduct seems to dislike the date range |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accounts functions
There are at least two methods to fix this problem
1) Add an IF around the sumif =if(C5<"",sumif(A1:a100,and(criteria1,criteria2,c riteria3),B1:b100),"") 2) Or add the blank cell as one of the criteria in the sumif sumif(A1:a100,and(criteria1,criteria2,c5<""),B1:b 100) "philn" wrote: Thanks for the rapid reply Joel!! So that future transactions can be included i have a number of empty cells at the end of the ranges for my criteria, which i really need to leave in as the dat will be refreshed on a monthly basis, so unfortunately using this i get an empty cell reference, any idea how i may be able to overcome this issue? "Joel" wrote: the criteria can be any boolean operation including a AND function =sumfi(A1:a100,and(criteria1,criteria2,criteria3), B1:b100) "philn" wrote: Hi I have imported data (nominal ledger transactions) from Sage 50 and need to query the results to total transactions for a particular nominal ledger code that fall between given dates. I have attempted to use sumif, but find i can only place 1 criteriea on it and sumproduct seems to dislike the date range |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accounts Receiveable Aging | Excel Worksheet Functions | |||
Excel for Accounts | Excel Discussion (Misc queries) | |||
how do i set up book keeping accounts? | New Users to Excel | |||
Where can I get a general template for T-accounts | Setting up and Configuration of Excel | |||
Setup Accounts Receivable functions in Excel til we get new syst? | Excel Worksheet Functions |