# DCountA without repeating criteria title

#### lmhudson

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

Replies
0
Views
256
Replies
3
Views
228
Replies
3
Views
1K
Replies
3
Views
1K
Replies
0
Views
785

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.

### 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