COUNTIF Function (selective)

[Gord]

Here is an odd one...

My company tracks several types of sales in a spreadsheet. On the same
page we have all these sales from the entire year. It's not pratical
to break them down by month. Using & and countif, I have got a way to
count the transactions by type, by month.

In one cell we report the date {call it A1}, in another the month ONLY
{B12}, and in a third the transaction type {C13}. In another hidden
cell I have B1&C1 and the countif funtion refers to the hidden colum
counting if D1=JANTYPE1.

Is there an eaiser way to count the contents of a cell depending on the
value of another cell in the same row? Basically, I could see it
spelled out as:

Count if the value of cell C1 = Type1 IF Cell A1 happened in the month
of Jan.

Problem is this sheet will have over 1000 rows of data entered on it.

Perhaps with some sort of named range that is dynamic depending on the
date in A?

 

[Max]

My company tracks several types of sales in a spreadsheet. On the same
page we have all these sales from the entire year. It's not pratical
to break them down by month. Using & and countif, I have got a way to
count the transactions by type, by month.
In one cell we report the date {call it A1}, in another the month ONLY
{B12}, and in a third the transaction type {C13}. In another hidden
cell I have B1&C1 and the countif funtion refers to the hidden colum
counting if D1=JANTYPE1.
Is there an eaiser way to count the contents of a cell depending on the
value of another cell in the same row? Basically, I could see it
spelled out as:
Count if the value of cell C1 = Type1 IF Cell A1 happened in the month
of Jan.
Problem is this sheet will have over 1000 rows of data entered on it.
Perhaps with some sort of named range that is dynamic depending on the
date in A?

Assuming your source data is in cols A & B,
with *real* dates in col A, TxnType in col B,
data from row2 down to say, a max expected row1500

Date TxnType
01-Apr-06 Type1
02-Apr-06 Type2
03-Apr-06 Type1
01-May-06 Type1
etc

and within say, D1:E3,
we have set-up the TxnTypes (in D13) & the Months (in E1:E3)
that we want the counts
(the "Months" are assumed input text such as: Jan, Feb, Mar, Apr, ... )

Type1 Apr
Type2 Apr
Type1 May
etc

we could put in F1:
=SUMPRODUCT((TEXT($A$2:$A$1500,"mmm")=E1)*($A$2:$A$1500<>"")*($B$2:$B$1500=D1))
and copy F1 down

Col F will return the required counts

Adapt the ranges to suit. The ranges have to be identical in size,
and we can't use entire col references (such as A:A, B:B)
in SUMPRODUCT. Use the smallest range size
large enough to cover the max expected extent
of data in cols A & B