DCountA without repeating criteria title

lmhudson

Board Regular
Joined
Nov 14, 2007
Messages
106
Is it possible to do this?

I have a spreadsheet with lots of data. I'm trying to do a transport analysis based on the following

Col B, title = "Store id" (Contains id for each store, circa 600 rows long) - Not relevant for the dcount, but what generates the number of rows
Col C, title = "1" (This is day num refering to Monday), values in column are 0 or 1
Col K, title = "trl" (Has all the different trailer sizes used)
Col L, title = "Depot" (Has 1 of 5 differetn depots)

I curently have this working as follows

Depot trl 1
A x 1
Depot trl 1
B x 1
Depot trl 1
C x 1

Depot trl 1
A Y 1
Depot trl 1
B Y 1
Depot trl 1
C Y 1
Etc, etc for all combinations of depots & Trailers & Days and a Dcounta on the trl field.
As I said earlier this works fine but looks very messy, is there a way to do it by just having 1 title row
i.e.
Depot trl 1
A x 1
B x 1
C x 1
A y 1
B y 1
C Y 1

Thanks
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
No, but you can use SUMPRODUCT, eg:

=SUMPRODUCT(--(Sheet1!L$2:L$600=A2),--(Sheet1!K$2:K$600=B2),--(Sheet1!C$2:C$600=C2))

Change the sheet and range references to suit, but make sure the ranges to sum all have the same number of rows and avoid using entire columns.
 
Upvote 0

Forum statistics

Threads
1,203,071
Messages
6,053,375
Members
444,658
Latest member
lhollingsworth

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