COUNTIF(AND???

[C.R.]

Hello,

I am not at all sure what Excel Function(s) to use here. Nothing I know of, which isn't much, is working.

I have a 2000+ line, multi columned list, sorted in ascending order by date for an entire month. The only two columns of data I'm interested in are the customer #'s and the $ amount of the orders they placed. What I'd like to do would be to count the number of orders each customer placed on the Mondays (11/7/2005, 11/14/2205, 11/21/2005 & 11/28/2005) of the month. Then do the same thing for the $ amount of each customer for each Monday. Hope this makes sense.

Thanks in advance,

C.R.

 

[nbrcrunch]

I don't understand the difference between the two criteria, but to get you started,

Weekday() is a a function that will return the day of week. When included in SUMPRODUCT() it can give you what you are looking for.

=sumproduct(--(weekday(MyDatesRange,2)=1),--(month(MyDatesRange)=11),--(MyCustomerList=TargetCustomer),MySalesRange)

The pseudo name ranges should make sense and you'll need to supply the real ranges.
TargetCustomer is the name of the customer you are getting totals on.

 

[Yogi Anand]

Hi C.R.:

Your description is clear enough. I suggest you post a few rows of your data, along with the expected results so we can see how your data is laid out ... and then let us take it from there!

 

[PaddyD]

add an extra column to the data to return the day, then use a pivot table:

Book1
	A	B	C	D	E	F	G	H	I
1	Who	Date	Val	Day				Data
2	a	1/01/2001	1	Mon		Who	Day	Count of Val2	Sum of Val
3	b	2/01/2001	2	Tue		a	Mon	2	16
4	a	3/01/2001	3	Wed			Tue	2	32
5	b	4/01/2001	4	Thu			Wed	2	20
6	a	5/01/2001	5	Fri			Thu	1	11
7	b	6/01/2001	6	Sat			Fri	2	24
8	a	7/01/2001	7	Sun			Sat	1	13
9	b	8/01/2001	8	Mon			Sun	2	28
10	a	9/01/2001	9	Tue		a Total		12	144
11	b	10/01/2001	10	Wed		b	Mon	2	30
12	a	11/01/2001	11	Thu			Tue	2	18
13	b	12/01/2001	12	Fri			Wed	2	34
14	a	13/01/2001	13	Sat			Thu	2	22
15	b	14/01/2001	14	Sun			Fri	1	12
16	a	15/01/2001	15	Mon			Sat	2	26
17	b	16/01/2001	16	Tue			Sun	1	14
18	a	17/01/2001	17	Wed		b Total		12	156
19	b	18/01/2001	18	Thu		Grand Total		24	300
Sheet1

 

[just_jon]

Book1.xls
	C	D	E	F
1	12/01/05	123	$ 123.00	2
2	12/02/05	456	$ 456.00	787
3	12/03/05	123	$ 789.00
4	12/04/05	789	$ 767.00
5	12/05/05	456	$ 454.00
6	12/06/05	789	$ 878.00
7	12/07/05	789	$ 99.00
8	12/08/05	123	$ 999.00
9	12/09/05	456	$ 666.00
10	12/10/05	456	$ 555.00
11	12/11/05	456	$ 444.00
12	12/12/05	456	$ 333.00
13	12/13/05	123	$ 22.00
14	12/14/05	890	$ 12.00
15	12/15/05	789	$ 1.00
16	12/16/05	789	$1,345.00
17	12/17/05	789	$ 4.00
18	12/18/05	789	$ 78.00
19	12/19/05	123	$ 0.99
Sheet3

 

[Aladin Akyurek]

Hello,

I am not at all sure what Excel Function(s) to use here. Nothing I know of, which isn't much, is working.

I have a 2000+ line, multi columned list, sorted in ascending order by date for an entire month. The only two columns of data I'm interested in are the customer #'s and the $ amount of the orders they placed. What I'd like to do would be to count the number of orders each customer placed on the Mondays (11/7/2005, 11/14/2205, 11/21/2005 & 11/28/2005) of the month. Then do the same thing for the $ amount of each customer for each Monday. Hope this makes sense.

Thanks in advance,

C.R.

=SUMPRODUCT(--(WEEKDAY(DateRange)=X2),--(DateRange-DAY(DateRange)+1=Y2),--(CustomerRange=Z2))

where X2 houses a weekday number of interest like 1 for Monday, Y2 the first day date of the month/year of interest like 1-Nov-2005, and Z2 a customer of interest.

This yields the desired count of placed orders.

For the order total...

=SUMPRODUCT(--(WEEKDAY(DateRange)=X2),--(DateRange-DAY(DateRange)+1=Y2),--(CustomerRange=Z2),AmountRange)

Note that the ranges cannot be whole column references like A:A.

 

[C.R.]

You guys are amazing.
I'll work with your suggestions as I am not too familiar with them.

Many thanks,

C.R.