Multiple Countifs with date complication

[LRA User]

I want to count the number of occurences involving 3 columns of a spreadsheet, but one of the columns is a date/time field, and I only want to analyse the date.

Example:

A B C
John 1 02/07/2009 09:45:10
Peter 1 03/06/2009 10:14:12
Fred 1
Peter
John 1 02/07/2009 09:45:10

How can I ask how many occurences of John are in column A, with a 1 in column B, with the date 02/07/2009?

 

[sanrv1f]

Hello and welcome to the board,

try this

Excel Workbook
	A	B	C	D	E	F	G	H
1	A	B	C
2	John	1	02-Jul-0909:45		John	1	02-Jul-09	2
3	Peter	1	03-Jun-0910:14
4	Fred	1
5	Peter
6	John	1	02-Jul-0909:45
Sheet2

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(--($A$2:$A$6=E2),--($B$2:$B$6=F2),--(INT($C$2:$C$6)=G2))

 

[LRA User]

Thanks sanrv1f!

Forgot to say a really vital part of the problem - I need the answer to be on a separate worksheet. Where would I put in the reference for that tab?

 

[sanrv1f]

just reproduce the sample solution into a new worksheet, cut and paste either the data part(A1:C6) or the result part(E2:H2) to another sheet, now you will get the reference to the other sheet in the formula

 

[LRA User]

I have a column which lists dates - how do I count the number between 2 dates, ie the month of August 08 would look for the dates between 1st August and 31st August 08.

 

[T. Valko]

One way:

=COUNTIF(A1:A100,">="&DATE(2008,8,1))-COUNTIF(A1:A100,">"&DATE(2008,8,31))

 

[LRA User]

Thanks for everyone who is helping me with this spreadsheet - but its just got more complex. As well as the date selection which I now have working, I'm interrogating another column for occurences of the number 1. Here's my formula but I'm getting negative numbers.

=COUNTIF('Data Dump'!$J$2:$J$6000,">="&DATE(2008,7,1))-COUNTIF('Data Dump'!$J$2:$J$6000,">"&DATE(2008,7,31))-COUNTIF('Data Dump'!$O$2:$O$6000, 1)

 

[T. Valko]

How do you want to handle the added condition of column O=1?

=COUNTIF('Data Dump'!$J$2:$J$6000,">="&DATE(2008,7,1))-COUNTIF('Data Dump'!$J$2:$J$6000,">"&DATE(2008,7,31))-COUNTIF('Data Dump'!$O$2:$O$6000, 1)

What your formula is doing is getting the total count of dates and then subtracting the total count of 1s. Is that what you want?

Or, do you want to count where the date is within the range *and* there is a 1 *on the same row* ?

 

[LRA User]

I want to count where the date is within the range *and* there is a 1 *on the same row* ?

 

[T. Valko]

Try this:

=SUMPRODUCT(--('Data Dump'!$J$2:$J$6000>=DATE(2008,7,1)),--('Data Dump'!$J$2:$J$6000<=DATE(2008,7,31)),--('Data Dump'$O$2:$O$6000=1))

Better to use cells to hold the criteria:

A1 = 7/1/008
B1 = 7/31/2008
C1 = 1

=SUMPRODUCT(--('Data Dump'!$J$2:$J$6000>=A1),--('Data Dump'!$J$2:$J$6000<=B1),--('Data Dump'!$O$2:$O$6000=C1))