countif/sumif issues

[trisoldee]

Hello,

My data is on Sheet2 Column A is transaction date and column B is number of units. My summary page has periods listed as Jan 1-31, Feb 1-28, etc... I'm trying to first count the number of transactions per month using

Please Login or Register  to view this content.

and second sum the number of units per month using

Please Login or Register  to view this content.

The transaction count seems to be working fine with the exception of Jan 1-31. It's returning a number in the millions when it should me 700.
The sum of units looks like it's working for April but the other months are all over the place.
I've attached the file also.
Book2.xlsx
The real reason I want to automate this is because prior month transactions can change, some can cancel which would reduce transactions, units and sales and sometimes an individual transaction can change which would move the number of units and sales. Ultimately I want to just have to copy and paste the raw data and then only need to look at the current month's activities because those will be listed by week. I also have to repeat the process about 4 or five times for different data sources. Any thoughts to what could be causing the issues I'm having or potentially another way to return the same info would be greatly appreciated.

TIA
Tristan

[azumi]

I see the file and nothing wrong with the formula, but you not ENTERing the formula with the right way, counting and summing formula is array formula,
you need to hit/press CTRL+SHIFT+ENTER button all together, doing this at the first result/row and if its succes you'll
see the bracket "{}" enclosed your formula, after that you can copied down as ecessary...... hope this clear

and adjust the ranges with data ranges, do not likethis =MONTH(Sheet2!A:A) this when you have plenty data,
should be MONTH(Sheet2!$A$2:$A$3145)

[Richard Buttrey]

Hi,

See attached.

With stuff like this you should always work with proper date numbers and not strings of text that happen to look like dates.

But the best way to analyse this is with a Pivot Table which I've also included.

[trisoldee]

I do have them entered as array formulas, not sure why they aren't showing the brackets, the problem is that the values the formulas are returning are not correct. If you filter the raw data to check it you will get different numbers. I'll keep looking. thanks.

[trisoldee]

Thanks Richard,

I agree with using a pivot my only issue there is that for the current month I have to break down by week. For the prior months though the ifS were actually going to be the next thing I was going to try.

[Richard Buttrey]

Hi,

You can still break the data down by weeks. Just Group the dates by day and set the start date and number of days to 7. Have a separate PT if you wish so that one groups by week and the other by month.

[trisoldee]

What do you mean when you say "set the start date and number of days to 7"? I was thinking that in order to do this I'd need to add a column to the raw data to show "week ending" and group accordingly. I've found this to do so

Please Login or Register  to view this content.

Let me know what you're talking about and thanks again.

[Richard Buttrey]

Hi,

In the Pivot Table select a cell in the date column and right click. Choose Group and select 'Days' then enter 7 in the number of days box and decide which is the first date you want your week summaries to start and enter that in the 'Starting at' box.

[trisoldee]

That's great thanks.