Counting when both match

[Greg D]

Here's where I need some help. I have a spreedsheet that will list off some
companyies like Company A, Company B. The next column has dates, like
1/25/06, 2/1/06.

The formula needs to only count how many times that Company A has a date
between 1/1/06 and 1/31/06. Then I'll repeat the formula for Company B. The
2 numbers have to match the total # of line entries in the worksheet.

Please help

[Ron Coderre]

Try something like this:

With your list in A1:B100
Where:
Col_A contains comany names
Col_B contains dates

D1: (start date)
E1: (end date)

F1: Company_A
F2: Company_B

G1: =SUMPRODUCT(($A$1:$A$100=F1)*($B$1:$B$100>=$D$1)*($B$1:$B$100<=$E$1))
Copy that formula down to G2

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Greg D" wrote:

> Here's where I need some help. I have a spreedsheet that will list off some
> companyies like Company A, Company B. The next column has dates, like
> 1/25/06, 2/1/06.
>
> The formula needs to only count how many times that Company A has a date
> between 1/1/06 and 1/31/06. Then I'll repeat the formula for Company B. The
> 2 numbers have to match the total # of line entries in the worksheet.
>
> Please help

[Pete_UK]

Assume your main data is in columns A and B, and suppose it covers row
1 to row 100. Use D1 for the start date (1/1/06), D2 for the end date
(1/31/06) and enter this formula in D3:

=SUM(IF(($A$1:$A$100="CompanyA")*($B$1:$B$100>=D1)*($B$1:$B$100<=D2),1,0))

This is an array formula, so once you have typed it in (or when you
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

The formula will count the number of times Company A exists in column A
with dates in column B which are between (and including) the dates in
D1 and D2. Change it to "CompanyB" for that count. If you have dates
which are outside the range, then the two counts will not match the
total number of entries.

Hope this helps.

Pete

[Greg D]

Thanks it worked.

"Pete_UK" wrote:

> Assume your main data is in columns A and B, and suppose it covers row
> 1 to row 100. Use D1 for the start date (1/1/06), D2 for the end date
> (1/31/06) and enter this formula in D3:
>
> =SUM(IF(($A$1:$A$100="CompanyA")*($B$1:$B$100>=D1)*($B$1:$B$100<=D2),1,0))
>
> This is an array formula, so once you have typed it in (or when you
> subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
> ENTER. If you do this correctly then Excel will wrap curly braces { }
> around the formula - you must not type these yourself.
>
> The formula will count the number of times Company A exists in column A
> with dates in column B which are between (and including) the dates in
> D1 and D2. Change it to "CompanyB" for that count. If you have dates
> which are outside the range, then the two counts will not match the
> total number of entries.
>
> Hope this helps.
>
> Pete
>
>