# How to count data in worksheet

#### ferrie_m

##### New Member
Hi,

I hope someone can help me here asap.

Below is my script to count number of names in worksheet.

=COUNTIF('WorksheetA'!I:I,"Critical")

Below is my scritp to count number of dates by range.

=COUNTIF('WorksheetA'!D:D,"<="&DATE(2011,2,1))-COUNTIF('WorksheetA'!D:D,"<"&DATE(2011,1,1))

Now I would like to combine this two formula to filter the date range and critical incidents in the excel sheet. And the Output will be on WorksheetB.

Can someone please assist me how to?

Thanks,

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

Try:

=SUMPRODUCT(--(WorksheetA!D1:D1000<=DATE(2011,2,1)),--(WorksheetA!D1:D1000>=DATE(2011,1,1)),--(WorksheetA!I1:I1000="Critical"))

Expand the range references to suit, but make sure that they all have the same number of rows and avoid using entire columns.

#### energman58

##### Well-known Member
In 2007 and above you can do COUNTIFS but you can use sumproduct to do teh same in all versions of excel

So something like

=SUMPRODUCT(--(K17:K19="Critical"),--(L17:L19>=O16),--(L17:L19<017))<O17))< p>
WHere you replace K17:K19 by your range of flags and L17:L19 by your date range

O16 and O17 contain the dates you are interested in

#### ferrie_m

##### New Member
Thanks very much to you both Andrew Poulsom and Energman58. I really appreciate your help.

I will try this today. :D

Cheers!

#### ferrie_m

##### New Member
Welcome to MrExcel.

Try:

=SUMPRODUCT(--(WorksheetA!D1:D1000<=DATE(2011,2,1)),--(WorksheetA!D1:D1000>=DATE(2011,1,1)),--(WorksheetA!I1:I1000="Critical"))

Expand the range references to suit, but make sure that they all have the same number of rows and avoid using entire columns.

It works!!! Thanks very much again Andrew. I appreciate your help :D

Replies
18
Views
378
Replies
1
Views
253
Replies
10
Views
506
Replies
13
Views
266
Replies
0
Views
200

1,191,274
Messages
5,985,692
Members
439,974
Latest member
sjoerdbosch

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