How to seperate and count credit and debit items based on dates?

You could probably do this with a SUMIF or COUNTIF formula in a small table, where you list the dates down the first column of the table and then the SUMIF formula can total the amounts for each day.

It is difficult to give you an exact formula to use, however, as you have given no details about how your data is laid out - perhaps you could attach a sample Excel workbook for us to look at (ensure there is no confidential data in it).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Its attached, Sir.

The sheet is protected, so I can't try out any formulae in it.

Pete

Whoops sorry, here it is unprotected

Oh that would work! But could it tell you an item count. She doesn't just want the total amounts, she wants the number of debit items and credit items. Thanks in advance!

Sorry for the delay in getting back to you - long phone call from my daughter.

I've set this up using formulae in the attached file. I've used columns L to P so that you can compare it with what you have derived, but really columns G to J are no longer needed.

I've derived the dates with this formula in L5:

=INT(MIN(D:D))

and this one in L6:

=IF(L5="","",IF(L5+1>MAX(D:D),"",L5+1))

which can then be copied down until you start to get blanks - this will then automatically adjust to your data. As your dates include times, I've had to use SUMPRODUCT instead of COUNTIF/SUMIF, so I've put this formula in M5:

=SUMPRODUCT((INT($D$2:$D$512)=$L5)*($E$2:$E$512>0))

for counting credits. The formula in N5 (for debits) is very similar:

=SUMPRODUCT((INT($D$2:$D$512)=$L5)*($E$2:$E$512<0))

I've then used the next two columns for the amounts, with this formula in O5:

=SUMPRODUCT((INT($D$2:$D$512)=$L5)*($E$2:$E$512>0),$E$2:$E$512)

and this in P5:

=SUMPRODUCT((INT($D$2:$D$512)=$L5)*($E$2:$E$512<0),$E$2:$E$512)

These formulae can then be copied down as far as you need to, and I've put some total formulae in row 2 which look at data down to row 500.

You should be able to apply this approach to any amount of data in columns A to F, but you will need to extend the ranges (from $512) if you have more data.

Hope this helps.

Pete