Counting data items

[Gord]

Just trying to find a way to tweak a data collection method my company
uses... Here's what we have now:

A sheet flagged as "others" that tracks each sale type (5 or 6
different transactions) and their sale date. So we'll have the client
name in A, the date in B, the month in C and the transaction type in D.
We have a hidden sheet in the workbook that has the following forumla:

=Others!C1&D1 (filled down to cover about 1500 transactions)

Now, on a summary sheet we have a count if function that says:

=COUNTIF(Hidden!A:A,"JanType") which gives us a running count of all of
each transaction done per month.

Rows along the top are for each month, and a summary for the quarter.
The COUNTIF function changes to inclue FEB, MAR, etc.

In a prefect would I would like a forumla to replace the one on summary
sheet that would allow us to count:
All of each type of transaction in D, but only for the month needed AND
i'd like to get rid of the redudent Column C and pull the month from
the date entry.

Ideas? Suggestions?

Since this paperwork is emailed in on a weekly basis using pivot tables
is not an option since it bumps the file size up too much...

 

[bt]

Hi,

You can dispense with the hidden sheet if you adopt array type formula.
For these to work you must press Shift Control Enter rather than simply
Enter when you insert the formula. I started a new sheet.

My b3 cell had a a date 1st Jan 2006 formated as MMM YY, then c3 had
the (normal) formual:-

=DATE(YEAR(B3),MONTH(B3)+1,DAY(B3)) with the same formating. You can
copy this across.

My A col has the five types listed down.

Then the body of the report has the following formula which the array
type so SHIFT CONTROL ENTER to get the curly brackets. So this is the
formula for B4 which you can copy across and then down (not both at the
same time with arrays as it wont let you overwrite the existing cell).

{=SUM(IF(Others!$D$4:$D$1500=AltSummary!$A4,IF(MONTH(AltSummary!B$3)=MONTH(Others!$B$4:$B$1500),IF(YEAR(AltSummary!B$3)=YEAR(Others!$B$4:$B$1500),1,0),0),0))}

So its adding up 1's for the sales of the type in A4, so long as they
fall in the month and year in b3

Regards,

Ben

 

[Gord]

Now, if I don't need to worry about the year since we use a new file
starting each Jan 1, will this work?

{=SUM(IF(Others!$D$4:$D$1500=AltSummary!$A4,IF(MONTH(AltSummary!B$3)=MONTH(Others!$B$4:$B$1500),1,0),0),0))}