Sumproduct usage?
 

My brain's on overload at the moment, so I'm turning to you guys for
help. I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.

Col D Col M
12/24/2009 Andy
12/24/2009 Charles
12/24/2009 Andy
12/25/2009 Charles

Sumproduct usage?
 

You had the right function in mind.

Try something like this...

Use cells to hold the criteria:

A1 = some date
B1 = some name

=SUMPRODUCT(--(D1:D10=A1),--(M1:M10=B1))

If you're using Excel 2007 then you can use the COUNTIFS function:

=COUNTIFS(D1:D10,A1,M1:M10,B1)

--
Biff
Microsoft Excel MVP


"Destrachan" wrote in message
...
My brain's on overload at the moment, so I'm turning to you guys for
help. I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.

Col D Col M
12/24/2009 Andy
12/24/2009 Charles
12/24/2009 Andy
12/25/2009 Charles

Sumproduct usage?
 

Excellent, Biff...thank you greatly. :)

On Dec 23, 12:07*pm, "T. Valko" wrote:
You had the right function in mind.

Try something like this...

Use cells to hold the criteria:

A1 = some date
B1 = some name

=SUMPRODUCT(--(D1:D10=A1),--(M1:M10=B1))

If you're using Excel 2007 then you can use the COUNTIFS function:

=COUNTIFS(D1:D10,A1,M1:M10,B1)

--
Biff
Microsoft Excel MVP

"Destrachan" wrote in message

...



My brain's on overload at the moment, so I'm turning to you guys for
help. *I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.

Col D * * * * * * * *Col M
12/24/2009 * * * *Andy
12/24/2009 * * * *Charles
12/24/2009 * * * *Andy
12/25/2009 * * * *Charles- Hide quoted text -

- Show quoted text -

Sumproduct usage?
 

Hi,

For a given date
=SUMPRODUCT((D1:D20=DATE(2009,12,24))*(M1:M20="And y"))

Over a range of dates
=SUMPRODUCT((D1:D20=DATE(2009,12,23))*(D1:D20<=DA TE(2009,12,24))*(M1:M20="Andy"))

In practice I would use cell ref's for the criteria
=SUMPRODUCT((D1:D20=A1)*(M1:M20=A2))

Mike

"Destrachan" wrote:

My brain's on overload at the moment, so I'm turning to you guys for
help. I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.

Col D Col M
12/24/2009 Andy
12/24/2009 Charles
12/24/2009 Andy
12/25/2009 Charles

.

Sumproduct usage?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Destrachan" wrote in message
...
Excellent, Biff...thank you greatly. :)

On Dec 23, 12:07 pm, "T. Valko" wrote:
You had the right function in mind.

Try something like this...

Use cells to hold the criteria:

A1 = some date
B1 = some name

=SUMPRODUCT(--(D1:D10=A1),--(M1:M10=B1))

If you're using Excel 2007 then you can use the COUNTIFS function:

=COUNTIFS(D1:D10,A1,M1:M10,B1)

--
Biff
Microsoft Excel MVP

"Destrachan" wrote in message

...



My brain's on overload at the moment, so I'm turning to you guys for
help. I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.

Col D Col M
12/24/2009 Andy
12/24/2009 Charles
12/24/2009 Andy
12/25/2009 Charles- Hide quoted text -

- Show quoted text -

Sumproduct usage?
 

Hi,

Create a Pivot table. Drag column D to the row area, column M to the column
area and column M (again) to the data area.

Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Destrachan" wrote in message
...
My brain's on overload at the moment, so I'm turning to you guys for
help. I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.

Col D Col M
12/24/2009 Andy
12/24/2009 Charles
12/24/2009 Andy
12/25/2009 Charles