Hi all,
This isn't Jet specific, but I am hoping one of you lovely people may be able to help me out, save me creating an account on another Excel forum.
I am creating a new sheet to record the stock level of our toner cartridges, and record when we book out a toner for a department.
We record the outgoing toners in one big list, and I want to count how many lines in the table exist for a specific model toner, booked out in a certain month, and then compare this to a value on another sheet.
I suspect it will be a combination of COUNTIF and VLOOKUP, but can't quite determine the correct syntax.
I have attached the example spreadsheet for reference, and have the additional info below:
The "Stock" sheet is where we manually update the in and out values for each model toner for each month.
Column A is the toner model, and alternating columns starting at column I, are the manual out counts for that model.
The "Control" sheet is where we record the outgoing toner, recording the model and date.
This is what I want to count from.
So in the "Stock" sheet I want to compare the value in column I (K, M, O etc.), for row 5 (Toner CE250X), to the count of lines on the "Control" sheet for model CE250X with a date in March.
Any advice would be greatly appreciated.
Eds
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Eds.
If you look at Sheet1 in the attached example, does that help get you started?
Note: you would have to work out how to handle your rolling months in order to correctly calculate the address listed in row 3 -
Jet Reports Historic Posts Rather than COUNTIF, I would consider using a COUNTIFS formula - this allows you to count based on multiple criteria.
For instance, using your spreadsheet, if I wanted to count the number of times that the first stock line was used AFTER given date I would do something like this:
Put the comparison date (in date format) into cell C2 of the stock sheet, and then in whatever cell you want to do this comparison, use this formula:
=COUNTIFS(Control!$A:$A,Stock!$A5,Control!$B:$B,">"&C2)
OR, if you want to go a step further, make the data on both of your tabs into Excel tables, so that the range can be updated as you add data to the sheet
then, your formula might look something like this:
=COUNTIFS(Table2[Toner Model],$A5,Table2[Date Changed],">"&Stock!C2)
Does that help any, or am I completely missing what you are trying to do? :) -
Jet Reports Historic Posts Thank you both for your replies, that definitely makes sense.
I can of course use this to retrieve a count, which I can compare to our OUT count on the "Stock" sheet, but I was wondering if this is something that can be used with conditional formatting?
I don't want to show the count really, I just want to make sure it matches the manual value we are entering on the "Stock" sheet for columns I K M O etc.
I tried a three colour scale, with one forumla being =$I$5<COUNTIFS(blah-blah), the middle being =$I$5=COUNTIFS(blah-blah) and the last being =$I$5>COUNTIFS(blah-blah), but doesn't seem to work.
Our goal is to try and indicate when the manual value we are changing in those columns, does not match the number of entries in the "Control" sheet, and conditional formatting seemed the most sensible way of doing this.
Cheers,
Eds
Please sign in to leave a comment.