Total Items Availble

[thyzt]

I am creating inventory management systems in excel where there is quantity, status (open or closed, where open is where the items is still in customers hand, and closed where they already return the item), date check-in, and date check out.

I am wondering if anyone can come up with the formula for the number of items available, where the status determine the total item available?

[mikeTRON]

Can you post a sample workbook with data and a example or two showing hard-coded numbers where you want the formulas to go?

[FDibbins]

Hi, welcome to the forum

Agree that a sample workbook would help, but take a look at the COUNTIFS() function

[thyzt]

Right now we only have one product to rent,
it will be really helpful if you guys can make a suggestion to improve my excel.

[mikeTRON]

I think what you are trying to do is sum the OPEN data points then subtract the CLOSED data points.

Try this.

Please Login or Register  to view this content.

If that isnt what you are wanting then you should provide a more concrete example with numbers and such in the example.

[thyzt]

Thank you, I am able to solve it with sumifs.

However, the example that i provide is the one that I am currently think of. I am not sure how to do it and don't think it is a good template because the JOB ID is redundant. I need to add the JOB ID twice in order to minus and added back the the total item.
So if anybody have a suggestion to improve my template or post a new template for me to use or to learn, feel free.

Thank you.

[mikeTRON]

Did the formula I posted above solve your problem? If not, what exactly is causing it to fail?

If you can create an example workbook that has data to test our logic with then we can help, otherwise I struggle to understand what exactly you are trying to do.
Ideally we would want to see adequate sample data, AND a hard-coded number (or numbers) to see what you are trying to do, and for us to prove our formula works as you want.


If you have any questions let me know I am glad to help.

[thyzt]

actually the sumifs is working thanks.

i will make another thread for another question

[mikeTRON]

Oh okay great.