vLookup to Count Number of Completed Category Items for Report

[rstarr1]

Hi All,

I'm sure there is just something I'm missing. I'm trying to put together a simple report to show me how many completed items (Item Status) a user (User ID) has within a specific category (Category). The report will show both the current month's data and the data for the past year.

I've attached a sample workbook with 3 tabs... Report, Month, Year

In the end, I'd want the report to look like this...

Sample View.JPG


I hope this makes sense...
Thanks.

[ChemistB]

In C2, for example, copied down
=COUNTIFS('Data (Month)'!$E:$E,"Business",'Data (Month)'!$B:$B,B2,'Data (Month)'!$D:$D,"Completed")

In D2
=COUNTIFS('Data (Year)'!$E:$E, "Business",'Data (Year)'!$B:$B,B2,'Data (Year)'!$D:$D,"Completed")

You might want to split the header into two cells (Month) or (Year) in one and the catagory in the other. That way you won't need to hardcode in "Business", you can just refer to C$1.
Make sense?

[rstarr1]

That did the trick... Thank you...

And then we realized we may need some additional data...
So... What the boss now wants; is to also know, for each user (User ID) how many completed items (Item Status) did they have each month (Date). Basically, how many completes (total) for each user, each month.

Any chance you can assist with this one as well?



Oh, and I've attached an updated workbook
Thanks!

[ChemistB]

What's the difference between tabs Data (Month) and Data (Year)? In reality, there will be a different Data (Month) for each month and then Data Year will be all months of 2015 (for example)?
Which brings me to the question, where do you want the completed items per month pulled from? Month? Year? or both?

[rstarr1]

Thanks for getting back.

The Data (Month) is just a subset of the Data (Year)... so, I would think the Data (Year) would be the source.

Oh, and here's the formula I was trying for January... I think I'm close???
=COUNTIFS('Data (Year)'!$E:$E, 'Data (Year)'!$B:$B,B2,'Data (Year)'!$D:$D,"Completed", 'Data (Year)'!$F;$F, ">="01/01/2015, 'Data (Year)'!$F:$F, "<"01/30/2015)




Thanks

[ChemistB]

Okay, first I modified the headers L1 to Q1 to be actual dates (i.e. 2/1/2015) but formatted as mmmm yyyy

Then in L2 copied across and down
=SUMPRODUCT(--('Data (Year)'!$B$2:$B$1000=$B2), --( 'Data (Year)'!$D$2:$D$1000="Completed"), --(MONTH( 'Data (Year)'!$F$2:$F$1000) =MONTH(L$1)))
Note, I didn't include row 1 of Data Year as it would generate an error when using the MONTH function.
Does that work for you? Questions?

[rstarr1]

That did it!
Thank you!