Count Text Cells with Multiple Criteria

M

m42

New Member
Joined
Dec 18, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I need to find the number of addresses used, associated with an order(last PO tab), and in a certain date range. How would I do that?



Consultant Inteview assessment v1 (1) - Copy.xlsx
ABCDEFGHIJKLMNOP
1Account #SupplierLast PO #First PO DateLast PO DateChannelBuyer NameBuyer Address NameAddressFirst PO #Last ASN PO #Last ASN DateFirst ASN DateLast Invoice #Last Invoice DateFirst Invoice Date
222872Steris Instrument Management Services, Inc.ExchangeTEST Regional Medical CenterTEST Regional Medical Center272 Test Data St
394520Siemens Healthcare Diagnostics IncAHS1151146/4/202110/4/21ExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data StAHS100438
47300-106693121Beckman Coulter IncExchangeTEST 2 Medical CenterTEST 2 Medical Center100 Extract Ln1092865732021-09-10 05:31:35 PM2021-08-20 06:11:22 PM
5408538-106693121Beckman Coulter IncExchangeTEST 3 Medical CenterTEST 3 Medical Center550 PO Street1093383222021-10-05 06:54:04 PM2021-07-07 09:32:08 PM
6199766Smith & NephewAHS1153796/2/202110/6/21ExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data StAHS100192
71016104MedlineAHS1154306/3/202110/6/21ExchangeTEST Fayette Medical CenterTEST Fayette Medical Center1430 Backorder AveAHS10017419638835542021-08-25 03:21:00 AM2021-06-03 01:35:23 AM
8822829388GraingerAHS1149096/10/202110/1/21ExchangeTEST Fayette Medical CenterTEST Fayette Medical Center1430 Backorder AveAHS10116290730456022021-10-02 09:37:58 AM2021-06-11 09:46:25 AM
9100289036AmerisourceBergen ABC OrderExchangeTEST Regional Medical Center3 Area Medical Center550 PO Street
10100266184AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST 2 Medical Center100 Extract Ln
11010019860AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST 2 Medical Center100 Extract Ln
12010000511AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST 2 Medical Center100 Extract Ln
13010235234AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST 2 Medical Center100 Extract Ln
14100272124AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
15100272123AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
16010234278AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
17010233494AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
18010179093AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
19010123356AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
20010051060AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
21010040691AmerisourceBergen ABC OrderExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data St
22101608-120565LivaNova USA, Inc.ExchangeTEST Regional Medical CenterTEST Regional Medical Center Receiving272 Test Data StAHS1070072021-08-03 12:58:32 PM2021-06-15 03:32:42 PM905242582021-08-03 09:11:03 PM2021-06-16 01:39:21 AM
Account Utilization
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Book1
ABCDE
1
2PO Date Range1/06/20211/10/2021
3Last PO #AHS115114
4Count1
5
6
7Account #SupplierLast PO #First PO DateLast PO Date
822872Steris Instrument Management Services, Inc.
994520Siemens Healthcare Diagnostics IncAHS1151144/06/20214/10/2021
107300-106693121Beckman Coulter Inc
11408538-106693121Beckman Coulter Inc
12199766Smith & NephewAHS1153792/06/20216/10/2021
131016104MedlineAHS1154303/06/20216/10/2021
14822829388GraingerAHS11490910/06/20211/10/2021
Sheet1
Cell Formulas
RangeFormula
B4B4=COUNTIFS($D$8:$D$28,">="&$B$2,$D$8:$D$28,"<="&EOMONTH($B$2,0),$C$8:$C$28,$B$3)
 
Upvote 0
Thank you, how would I modify this formula if I wanted to see, 6, 12, and 18 months separately?
 
Upvote 0
Count Text Cells with Multiple Criteria_m42.xlsx
ABCDEF
1Start4 months6 months12 Months18 Months
2PO Date Range1/06/202131/10/202131/12/202130/06/202231/12/2022
3Last PO #AHS115114
4Count1111
Sheet1
Cell Formulas
RangeFormula
C2C2=EOMONTH($B2,4)
D2D2=EOMONTH($B2,6)
E2E2=EOMONTH($B2,12)
F2F2=EOMONTH($B2,18)
C4:F4C4=COUNTIFS($D$8:$D$28,">="&$B$2,$E$8:$E$28,"<="&C$2,$C$8:$C$28,$B$3)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff
R
  • Solved
Stumped on counting patients unique visits to a clinic
Replies
7
Views
539
Fluff
Fluff
C
  • Question Question
conditional formatting based on multiple criteria
2
Replies
13
Views
406
dreid1011
dreid1011
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
101
welshgasman
welshgasman
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
544
sspatriots
S

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top