Need help with multiple criterias on a sheet

Ama

Hi All, i need help on setting multiple criterias as im a bit stuck.

I m trying to count how many specific batteries "Super battery V3X-V4" are available, and i want to exclude recycled, refurbished and lost that have been ticked. because at the minute its only counting the number of v4 batteries that are in that exact range (D22:D20000), once i know the exact formula i will add it to the other specific batteries.

another issue i found is that i have used the formula "=D12-SUM(D14,D15,D16)" to count how many "Total number of batteries available" i have in the sheet, but when i tick both "to be recycled" and "to be refurbished" it removes them both having an incorrect data. Can i impose a criteria in both checklists "to be recycled" and "to be refurbished" that if one is ticked the other cannot so it wont affect the data for available batteries?

Also, I'm trying to set up a command where If I scan a duplicate serial number, it shows up and tells me how many are duplicate, but specific serial numbers, not in total. i have set up the criteria that the box changes colour if duplicate, but when you deal with thousands of batteries, it gets a bit of an issue, even if I use a filter, it still takes time, is there an easier way to catch on a separate sheet\cell the exact serial numbers that are duplicate and find them on the coloumn to then delete edit?

Thank you so much for your help

Hollie Green

In order to easily filter to the rows you need to delete I would add a column type #Auto Row. In my formula I named it Row ID

I then created a helper column to put my formula in depending on how your have your duplicate formula set up for your conditional formatting you may need to change the from greater than 1 to an equal to 1. I set mine up just to count the duplicates but if you have your's set up as a check box for duplicates or to just put a 1 if it is a duplicate you will need to change the formula slightly

=IF(COUNTIFS([Serial N.]:[Serial N.], [Serial N.]@row, duplicate:duplicate, >1, [Row ID]:[Row ID], >[Row ID]@row) > 0, "Delete", "")

Any that are labeled as Delete can be deleted. I would verify first to ensure it is working correctly but what it does is label all of the ones except the most recent submission as delete.

Hollie Green

This one took a little longer to figure out but the below will give you a count if Model number matches the Model number and subtract any that has a check mark in To be refurbished, To Be Recycled . It will only subtract once even if both boxes are checked in the same row.

=COUNTIF(Model:Model, Model@row) - COUNTIFS(Model:Model, Model@row, [To Be Refurbished]:[To Be Refurbished], 1, [To Be Recycled]:[To Be Recycled], 0) - COUNTIFS(Model:Model, Model@row, [To Be Refurbished]:[To Be Refurbished], 0, [To Be Recycled]:[To Be Recycled], 1) - COUNTIFS(Model:Model, Model@row, [To Be Refurbished]:[To Be Refurbished], 1, [To Be Recycled]:[To Be Recycled], 1)