Help with countif and sumproduct

m42

New Member
Joined
Dec 18, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to take data from one tab (multiple suppliers but only certain accounts being utilized based on an order) and populate into a table how many have been used for a certain date range. I want to exclude the blank cells in the count. I was trying to use the sumproduct and countif formula but feel I am missing something.

Thank you.
 

Attachments

  • e.JPG
    e.JPG
    21.8 KB · Views: 11

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Welcome to the MrExcel board!

That image is not enough for us to tell what your have, where it is or what result you actually expect. Perhaps you could give us another set of dummy data, and the expected results, with XL2BB and explain again in relation to that new sample data?
 
Upvote 0
test excel.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
231000214237Vascular Solutions IncMetaTradeTEST Regional Medical Center3 Area Medical Center550 PO Street
241000018737Vascular Solutions IncMetaTradeTEST Regional Medical CenterTEST 2 Medical Center100 Extract Ln
251000012457Vascular Solutions IncMetaTradeTEST Regional Medical CenterTEST Regional Medical Center272 Test Data St
26204646Richard Wolf Medical Instr.AHS1142456/2/20219/27/21MetaTradeTEST Regional Medical CenterTEST Regional Medical Center272 Test Data StAHS100149
27209277Richard Wolf Medical Instr.MetaTradeTEST Regional Medical CenterTEST 2 Medical Center100 Extract Ln
28214134Richard Wolf Medical Instr.MetaTradeTEST Regional Medical Center3 Area Medical Center550 PO Street
Account Utilization
 
Upvote 0
I'm trying to use that data to fill in this table, some vendors are listed multiple times(diff accts) but no orders placed. So I would need to exclude them.
 

Attachments

  • e.JPG
    e.JPG
    29 KB · Views: 8
Upvote 0
What would be the best formula to use to show the total Unique Registered vendors(ones that have a PO #)? I was thinking I could run a filter to see which ones were used based on a date then count them?
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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