Date counting items??

[Ben1985]

Hello,

Say I have a date range of one week and I want to add up how many items were sold on any given day how would I do this?

[Ace_XL]

Perhaps use COUNTIF or SUMIF depending on what your data is

=COUNTIF(Week_Range,Date)

=SUMIF(Week_Range,Date,Sum_of_items)

A sample file will help

[Ben1985]

Thanks for the help. I've uploaded a sample file

Thanks,

[Special-K]

in A18
jumper

in A19
=COUNTIF(A1:A16,A18)
for number of items

in A20
=SUMIF(A1:A16,A18,B1:B16)
for cost of items sold

[Ben1985]

Hey, that gives the total for the number of items but not the number of items on a given date (18/09/12 in this example)?

[FDibbins]

see if the attached give you what you want? Note that for the name "socks", 1 entry has a space at the end, the other does not.

i have given you a total sum and count by day, as well as a total sum and count by date and name. use whichever you need

[Ben1985]

Sorry all,

I haven't made myself very clear.

I've attached another sample. I want the number of 'exchanges' to be counted that happened on that day.

see attached.

Thanks,EXAMPLE1.xlsx

[Ace_XL]

try either of the two..

=SUMPRODUCT(--(DATE(YEAR($B$4:$B$192),MONTH($B$4:$B$192),DAY($B$4:$B$192))=DATE(YEAR(E5),MONTH(E5),DAY(E5))))
or
=COUNTIFS($B$4:$B$192,">"&E5,$B$4:$B$192,"<"&(E5+1))

[Ben1985]

Hey thanks Ace, would you be able to put that into the spreadsheet for me? It's confusing me when trying..

Thanks,

[Ace_XL]

See attached. Does this help?

[Ben1985]

Ar thanks. Sorry to keep asking questions. This forum is so helpful!

How do those formulas know to look up 'exchanges'? I'm asking so when I add refunds to the mix I want the formula to be able to pick those up too.

[Ace_XL]

How do those formulas know to look up 'exchanges'? I'm asking so when I add refunds to the mix I want the formula to be able to pick those up too.

I am sorry, I do not understand. Could you post a 'complete (in all respects)' sample with expected results

[Ben1985]

six.xlsxhello, yes sorry.

see attached. i want to be able to change the varibles the forumla it's looking up. In the report attached the report has both refunds and exchanges in.

[Ace_XL]

Change the titles G5 and H5 to read EXCHANGE and REFUND respectively.
Add in another criteria in the COUNTIFS.


Hence,
In G6
=COUNTIFS($A$5:$A$499,G$5,$C$5:$C$499,">"&$F6,$C$5:$C$499,"<"&($F6+1))
In H6
=COUNTIFS($A$5:$A$499,H$5,$C$5:$C$499,">"&$F6,$C$5:$C$499,"<"&($F6+1))

and copy down. See attached

[Ben1985]

Thank you.