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

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

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

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

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