Counting with Critera

[mamaro]

What I want to do: Count how many times a Employees name shows up i
"data" after the "date of letter" which is manually updated on "Mem
info" worksheet. Then have this count show up in my report. I have cam
up with this temporary solution. What I do is lookup employees name o
"memo info" if they are there return date of letter (see Comparison)
Then I do an if statement which says if Date of trans is less than o
equal return employee name (see Result). on my report I have employee
name usually as a pivot chart from "data" then I do a lookup that find
employees name and returns date of letter. this brings me to where al
my calculations work, on "report" I do a count of how many time
employees name shows up in "result"! I am a newbie to Excel. it take
excel 2min to calculate a month worth of data. in the future I wil
need it to calculate 9 months worth of information for one letter
have 5 other letters i have to keep track of also. Thanks in advanc

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=43057

 

[steve smallman]

mamaro,

I think you are pretty well on the right track.

To address your needs, I would:
Data Sheet;
Remove column C
Adjust formulae in resulting column C to
read "=B2>=VLOOKUP(A2,'Memo Info'!A:B,2,FALSE)"
Remove resulting column D

Report Sheet;
Adjust formulae to read "=IF(ISNA(VLOOKUP(A2,'Memo Info'!
A:B,2,FALSE)),"",VLOOKUP(A2,'Memo Info'!A:B,2,FALSE))"
Insert Pivot Table based on Data sheet with employee name
as the Row heading comparison as the column heading and
date of letter as the value, then adjust to show only
value "True".

From your post you will have multiple letter types, and
comparison can be renamed letter1, with similar formulae
for each letter date.

Realistically, all I am suggesting is simplifying the
formulae a little, and using a pivot table to do your
summarisation. Well done!

Steve

 

[Frank Kabel]

Hi
I'd suggest the following on you worksheet 'Report'. Instead of using
COUNTIF which uses the result of an IF function in comnination with
VLOOKUP try the following formula in C2 on your report sheet:
=SUMPRODUCT((Data!$A$2:$A$20=Report!A2)*(Data!$B$2:$B$20<=Report!B2))
copy down. With this you can delete the columns D and E on your data
sheet.
Though I have one question. currently you're counting all instances in
you sheet data where TRANSDATE<=date of letter (from your heading I'd
assume it vice versa)?


For your issue with #NA: chnage the formula in B2 as follows:
=IF(ISNA(VLOOKUP(A2,'Memo Info'!A:B,2,FALSE)),"",VLOOKUP(A2,'Memo
Info'!A:B,2,FALSE))
or use conditional format: Select column b and entere the following in
conditional format as formula: =NA() and format the cell with a white
font color
The latter one would be faster (as the VLOOKUP has to be calculated
only once)

HTH
Frank