Count rows that match criteria in 2 different column cell ranges
 

I have a spreadsheet that lists all customer orders for the year - each order
is a row. I need to count the number of orders per customer per month.

I'm having trouble figuring out what formula to use to count the number of
rows in which the customer column (a range named "customer") = "xxx" and the
month column (a range named "month") = 1 (for January).

I've tried several combinations of things and keep getting errors. I've
tried ROWS, COUNTIF, IF with COUNTIFs and/or ROWS, etc. to the point that I'm
thoroughly confused. I'm sure this is probably simple but I just can't seem
to see it at this point.

Thanks for your help!
--
JoAnn

Count rows that match criteria in 2 different column cell ranges
 

Enter the customer name in D1, and the number of the month in D2, and try
this:

=SUMPRODUCT((customer=D1)*(MONTH(month)=D2))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"JoAnn" wrote in message
...
I have a spreadsheet that lists all customer orders for the year - each
order
is a row. I need to count the number of orders per customer per month.

I'm having trouble figuring out what formula to use to count the number of
rows in which the customer column (a range named "customer") = "xxx" and
the
month column (a range named "month") = 1 (for January).

I've tried several combinations of things and keep getting errors. I've
tried ROWS, COUNTIF, IF with COUNTIFs and/or ROWS, etc. to the point that
I'm
thoroughly confused. I'm sure this is probably simple but I just can't
seem
to see it at this point.

Thanks for your help!
--
JoAnn

Count rows that match criteria in 2 different column cell ranges
 

JoAnn: This may sound difficult but, it is really very easy. Lets assume
your data looks like this...

Customer Month
JOE 1
JOE 1
LARRY 1
LARRY 1
LARRY 1
KRISTI 1
JOE 2
HAYDEN 2
LARRY 2
KRISTI 2

You will a title above each column (Customer, Month). Click on the top,
left cell of your data, in this case "Customer". On your toolbar, click
"Data", then click "Pivot Table and PivotTable Chart". In the dialogue box
that appears, click "Next", then "Next" again, then click "Layout". Drag the
"Customer" box on the right over to the Row area, then drag the "Month" box
over to the Row area and drop it under Customer. Then drag the "Customer"
box (again, from the right) over to the Data area. Once you drop it, it
should read "Count of Customer". If it doesn't, you can double-click it and
change its function. Then click OK. Now select where you want the Pivot
Table (a new sheet, or the existing one) and click Finish. Your result will
look like this...

Count of Customer
Customer Month Total
HAYDEN 2 1
HAYDEN Total 1
JOE 1 2
2 1
JOE Total 3
KRISTI 1 1
2 1
KRISTI Total 2
LARRY 1 3
2 1
LARRY Total 4
Grand Total 10

So, by month, by customer, you get a count of customers. Hope this helps.

"JoAnn" wrote:

I have a spreadsheet that lists all customer orders for the year - each order
is a row. I need to count the number of orders per customer per month.

I'm having trouble figuring out what formula to use to count the number of
rows in which the customer column (a range named "customer") = "xxx" and the
month column (a range named "month") = 1 (for January).

I've tried several combinations of things and keep getting errors. I've
tried ROWS, COUNTIF, IF with COUNTIFs and/or ROWS, etc. to the point that I'm
thoroughly confused. I'm sure this is probably simple but I just can't seem
to see it at this point.

Thanks for your help!
--
JoAnn