How to count data in worksheet

[ferrie_m]

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,

 

[Andrew Poulsom]

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]

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]

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

I will try this today. :D

Cheers!

 

[ferrie_m]

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