# COUNTIF(AND???

#### C.R.

##### Board Regular
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.

C.R.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

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!

add an extra column to the data to return the day, then use a pivot table:
Book1
ABCDEFGHI
1WhoDateValDayData
2a1/01/20011MonWhoDayCount of Val2Sum of Val
3b2/01/20012TueaMon216
4a3/01/20013WedTue232
5b4/01/20014ThuWed220
6a5/01/20015FriThu111
7b6/01/20016SatFri224
8a7/01/20017SunSat113
9b8/01/20018MonSun228
10a9/01/20019Tuea Total12144
11b10/01/200110WedbMon230
12a11/01/200111ThuTue218
13b12/01/200112FriWed234
14a13/01/200113SatThu222
15b14/01/200114SunFri112
16a15/01/200115MonSat226
17b16/01/200116TueSun114
18a17/01/200117Wedb Total12156
19b18/01/200118ThuGrand Total24300
Sheet1

C.R. said:
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.

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.

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

Many thanks,

C.R.

Replies
7
Views
247
Replies
1
Views
313
Replies
1
Views
277
Replies
5
Views
288
Replies
0
Views
224

1,196,098
Messages
6,013,441
Members
441,766
Latest member
ixruiz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back