Thread: SUMPRODUCT formula with multiple locations of data

I can't get this to work. Here is the formula:
=SUMPRODUCT(--('OCT TO OCT'!B6:B5125>=DATEVALUE("1/01/2010")),--('OCT TO OCT'!B6:B5125<DATEVALUE("2/01/2010")))+SUMPRODUCT(--('NOV 10'!B6:B5126>=DATEVALUE("1/01/2010")),--('NOV 10'!B6:B5126<DATEVALUE("2/01/2010"))) AND SUMPRODUCT(--('OCT TO OCT'!I6:I5125>=DATEVALUE("1/01/2010")),--('OCT TO OCT'!I6:I5125<DATEVALUE("2/01/2010")))+SUMPRODUCT(--('NOV 10'!J6:J5126>=DATEVALUE("1/01/2010")),--('NOV 10'!J6:J5126<DATEVALUE("2/01/2010")))

I am trying to have a count of the cells that match before and after the word AND. This is a monthly calulation. Rows of data are entered at the bottom of each spreadsheet regardless of the dates. But I need to know how many cells match the date in each column.

Example:
Date Admitted 10-2-10 Date Approved 12-4-11
Date Admitted 10-28-10 Date Approved 10-29-10
Date Admitted 6-17-09 Date Approved 10-01-10

I need to know how many cells were admitted AND approved in October 2010.

The new information is always added at them bottom which doesn't go in order by either date.

Thanks in advance for any help on this! I need to get this working for a report and there are over 8000 records of information!

MCAZ

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

You mean Admitted and Approved within the same month? Out of 190+ records you show there are not 147, that is for sure....

I count 9 that have both cells in Oct-09

Using formula:

=SUMPRODUCT(--(TEXT($A$2:$A$193,"mmm-yy")="Oct-09"),--(TEXT($E$2:$E$193,"mmm-yy")="Oct-09"))

The text formula converts a numeric entry into a specific text format.

So in this case I am converting all the dates in A2:A193 into text formats of mmm-yy which means 3-letter month and 2 number year format (eg. Oct-09) means October 2009

So it checks if any of the cells (when converted) equal "Oct-09" which is the same as saying greater than or equal Oct 1, 2009 and less than Nov 1, 2009

Did you test the formula and count the results.

You can also use (in XL2007+)

=COUNTIFS($A$2:$A$193,">="&DATE(2009,10,1),$A$2:$A$193,"<"&DATE(2009,11,1),$E$2:$E$193,">="&DATE(200 9,10,1),$E$2:$E$193,"<"&DATE(2009,11,1))

See attached formulas in G1:H1