Counting problem

[Alasbabylon]

I am trying to figure out how to count items on a second page of a file and report those items on the first page. We need to count how many time a specific person in Col. A has a value greater than 14 when the date in Col. G is more than 14 days older than today
This is one formula I am using for another task in this spreadsheet. I tried to adapt it, but did not have much luck.

=SUMPRODUCT(--('POD Adhoc Report'!$B$5:$B$511="Doe, Jane"),--('POD Adhoc Report'!$C$5:$C$511="SLA"))

Now I need a formula that will check the same range for the name, and check column G for a date, compare that date to today's date, calculate the number of days between those two dates, and return the number of records for Jane Doe that are greater than 14.

Note: Column G has text and the date - could that be throwing things off?
And, can I check the whole column instead of the specific range?

I've tried adapting this formula, but am not getting something right.
HELP!

[Zack Barresse]

Hi,


You cannot do an entire column using SUMPRODUCT. Whilst I know this query of yours is quite possible, can you give some examples of what may be found in column G? If you could, provide a few examples of data; a few that are "good" data and will be counted, and a few that are "bad" data that will not be counted.

[Alasbabylon]

Col B will have a list of names, I need to count, for each person, how many files they have that have not been updated in the past 2 weeks. The data in the date column will read "Entered by Jane Q. Doe on 12/14/2004 9:06:03" (or whatever name is being reported.)
I've included a copy of the file.

I will also have to then figure out how many files aech person has that is due by a certain date, but thought I would be able to get it figured out from this formula. (I hope)

[Zack Barresse]

I don't see a file attached. It must be a zip file. Also, try to seperate your dates and keep them in a seperate column. This will make your data much easier to diseminate and work with. Especially if Excel can recognize the dates as true dates. If it see's text, it make it more difficult.

[Alasbabylon]

I can't change the data coming in, but I can do a short macro to delete the unnecessary text and reformat.

Sub FixDates()
' FixDates Macro
' Macro recorded 01/14/2005 by Marci Abels
' Keyboard Shortcut: Ctrl+z
Columns("G:G").Select
    Selection.Replace What:="Entered by * on ", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "m/d/yyyy"
End Sub

And you're right - I didn't zip the file. Here it is.

[Zack Barresse]

How about something like this ...

=SUMPRODUCT(--('POD Adhoc Report'!$B$5:$B$511="Abels, Marci A."),--('POD Adhoc Report'!$G$5:$G$511<TODAY()))

Although I would suggest a couple of things.

1) Change the names in column A from "Marci" to "Abels, Marci A.". That way you can just reference $A2 instead of a hardcoded name.
2) Do the same thing with your column headers as suggestion 1. Instead of "0%", use B$1.

If you do these two things, it should be one formula in the upper left corner, then drag over and down. You would need to have your headings the same as the criteria you're checking though.

And I edited your post to include the [ VBA ] tags. They are used to make your code look the way it does. Hope you don't mind. Thanks go to Mark007 for coding that option for our board.


HTH

[Alasbabylon]

This is fantastic! But - it counts anything older than today. How do I make it count only the items that are more than 14 days old?

[Alasbabylon]

WAIT! can it be as easy as this?

=SUMPRODUCT(--('POD Adhoc Report'!$B$5:$B$511=A12),--('POD Adhoc Report'!$G$5:$G$511<TODAY()-14))

[Zack Barresse]

Yes. Sorry.

I'ma dork like that sometimes. So, yes! Just subtract the 14 from today.

[Alasbabylon]

AWESOME firefytr! I'm doing the Happy Dance in my little Dilbertsville cube right now!

[Zack Barresse]

Great!

Btw, did you know that you can mark your own threads as Solved? Just go to Thread Tools --> Mark Solved --> Perform Action. Credit goes to Mark007 for creating that especially for VBAX!