Create a count of cells for unique locations by months

[nserrano]

Hi everyone,

Happy Monday to you all. I need to build on the formulas that were created last week for counting visit statuses per location (with many thanks to you for help!) - Now the need is to count the number of visits that fall within a date range for each unique location. For example, with the following data on Tab 2:

	A	B	C	D	E	F	G	H	I	J	K	L	M
Location	ID	Site Visit 1	Visit 1 Status	Site Visit 2	Visit 2 Status	Site Visit 3	Visit 3 Status	Location	Nbr of ^	Nbr of #	Nbr of *
ABCD-123	^	#	*
ABCD-456	#	#	*
ABCD-789	^	^	*
WXYZ-098	^	#	#
WXYZ-765	#	^	#
WXYZ-432	*	^	^
LMNO-123	#	*	#
LMNO-456	*	^	*
LMNO-789	^	#	*

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1-Mar-14
[/TD]

[TD="align: right"]5-Apr-14
[/TD]

[TD="align: right"]10-May-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1-Mar-14
[/TD]

[TD="align: right"]10-May-14
[/TD]

[TD="align: right"]4-July-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]5-Jan-14
[/TD]

[TD="align: right"]4-Feb-14
[/TD]

[TD="align: right"]10-Mar-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]3-Feb-14
[/TD]

[TD="align: right"]10-Mar-14
[/TD]

[TD="align: right"]15-May-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]6-Apr-14
[/TD]

[TD="align: right"]1-May-14
[/TD]

[TD="align: right"]4-June-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]10-Mar-14
[/TD]

[TD="align: right"]1-May-14
[/TD]

[TD="align: right"]4-June-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"]16-Apr-14
[/TD]

[TD="align: right"]15-June-14
[/TD]

[TD="align: right"]15-July-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"]1-Mar-14
[/TD]

[TD="align: right"]1-Apr-14
[/TD]

[TD="align: right"]5-May-14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"]10-Apr-14
[/TD]

[TD="align: right"]1-May-14
[/TD]

[TD="align: right"]15-June-14
[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

We would need an output on Tab1 of:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Location
[/TD]
[TD]Site Visits in Mar 2014
[/TD]
[TD]Site visits in April 2014
[/TD]
[TD]Site visits in May 2014
[/TD]
[TD]Site visits in June 2014
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]


My existing formulas are:
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="*"))
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="#"))
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="^"))

In this new calculation, status doesn't matter, so I have

1. just taken one of the formulas above: =SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="#"))
2. and would like to add a search for cells that contain the string "Site": SUMPRODUCT(--ISNUMBER(SEARCH({"site"},Tab 2'!$A1:$H1)))>0)
3. and also the condition that the dates must fall within a date range to be counted: IF('Tab 2'!C1:H10>=DATE(2014,03,01)) AND ('Tab 2'!C1:H10<=DATE(2014,03,31)) (and the same for ranges of April 1 - April 30, May 1 - May 31 and June 1 and June 30)

How can I combine these correctly?

Thanks for any help you can provide!

Natasha

 

[nserrano]

Hi everyone,

Would an array of dates help with this calculation? And can an IF statement be nested within a SUMPRODUCT statement?