Hello... I'm trying to perform a function that I cannot for the life of me
figure out. I'm using Excel 2003. Here's what I have: in Column A I have 4
different data entries (i.e. 502D, 502L, 504D and 504L) recurring throughout
all of its 220 rows. Only one of those entries will be in each cell and all
cells contain one. Now in Column D, every time there is a cell that contains
a date (an "approval date") in it in the same row as one of those entries, I
need it to count how many cells have dates in them for each of those entries
individually and tally the total approval dates for that individual entry
(i.e. "502D"). There aren't many approval dates (only about 150 of the 220
rows have them). So bascially, i need it to act like COUNTIF, but ONLY count
how many times cells have dates in them in D and for EACH entry code it
occurs for. I then need it summed at the bottom (i.e. 502D approval dates
total occurances in D222, 502L approval dates total occurances in D223,
etc...).
I was playing around with sumproduct and got close, but no cigar. Any ideas???

Extract unique values in Col A to Col to any unused Col, say Col F
(by Data|Advanced Filter... Unique Records only Copy to another location F1)

then in G1 enter
=SUMPRODUCT(--($A$1:$A$220=E1),--($D$1:$D$2200))
and copy down till end of your data set
Note: 0 will work as any cell having dates will return TRUE

For GRAND TOTAL addup Col G
--
Always provide your feedback...


"upnadam" wrote:

Hello... I'm trying to perform a function that I cannot for the life of me
figure out. I'm using Excel 2003. Here's what I have: in Column A I have 4
different data entries (i.e. 502D, 502L, 504D and 504L) recurring throughout
all of its 220 rows. Only one of those entries will be in each cell and all
cells contain one. Now in Column D, every time there is a cell that contains
a date (an "approval date") in it in the same row as one of those entries, I
need it to count how many cells have dates in them for each of those entries
individually and tally the total approval dates for that individual entry
(i.e. "502D"). There aren't many approval dates (only about 150 of the 220
rows have them). So bascially, i need it to act like COUNTIF, but ONLY count
how many times cells have dates in them in D and for EACH entry code it
occurs for. I then need it summed at the bottom (i.e. 502D approval dates
total occurances in D222, 502L approval dates total occurances in D223,
etc...).
I was playing around with sumproduct and got close, but no cigar. Any ideas???