Formulas and Dates

**graybush** · 08-04-2011, 02:36 PM

Hi,

I am trying to sum unique names in a column with a specific date. Thanks to some help from one of the gurus here I was able to successfully get a sum. I have the formula setup for a specific date but when I change the date I get the #VALUE! error. I know that I am not explaining this very well so I have included a file.

The first formula works and looks like
=SUMPRODUCT(1/COUNTIF(IF(B1:B40=DATE(2010,1,31),A1:A40,CHAR(1)),IF(B1:B40=DATE(2010,1,31),A1:A40,CHAR(1))))-1

However the second does not and looks like
=SUMPRODUCT(1/COUNTIF(IF(B1:B40=DATE(2009,2,22),A1:A40,CHAR(1)),IF(B1:B40=DATE(2009,2,22),A1:A40,CHAR(1))))-1

The only thing that is different is the two dates. Please see attached file for further details.

Thanks in Advance

**Azam Ali** · 08-06-2011, 12:46 AM

Hi graybush
find a workaroung in the attach file. it may be helpfull

**DonkeyOte** · 08-06-2011, 03:52 AM

To count unique names for a given date given your version (XL2010)

Please Login or Register  to view this content.

However, you would be best served storing the date value in an adjacent cell (say D1) and referencing that cell in the formula in place of the explicit DATE call. This approach would permit you to simply copy the formula down rather than manually altering each formula for a different date.

For backwards compatibility (ie pre COUNTIFS) you can use either:

Please Login or Register  to view this content.

In the above I have used D1 to store the Date value of interest - alluded to previously.

**graybush** · 03-15-2012, 09:32 PM

Thanks for help...is it possible that you could walk me through exactly what the formula below is doing? It works and all I am just looking to understand better how it works...

=SUMPRODUCT(($B$1:$B$40=$D1)*(MATCH($A$1:$A$40&"@"&$B$1:$B$40,$A$1:$A$40&"@"&$B$1:$B$40,0)=(ROW($A$1:$A$40)-ROW($A$1)+1)))

Thanks