Excel formula help, image attached.

[MorgUK]

I'm trying to create an inventory spreadsheet on excel using barcodes that will be scanned in.

For each barcode that matches, I want the inventory to decrease by 1.


Right click and "view image" if it's small.

As you can see the inventory reads 49, from the original 50, but there are 6 barcodes scanned, so it should be 44. I can't think of a formula to make it work. I need it to "-1" but to recur on each new barcode.

Once I can get this formula sorted I will add different products and the formula should be able to dictate which inventory to deduct.

Thanks for the help!

[MadHorseman]

Not sure but shouldn't the 1 in the "G3-1" bit be the result of the countif? Or do you get a circular error?

[MorgUK]

Well the current formula isn't giving any errors, but it will only count 1 barcode because I can't get it to -1 from the new figure. It always does 50-1, instead of 50-1, 49-1, 48-1 etc.

[Joe Schmoe]

I don't think Excel runs on the Pi. Am I missing something here?

[MorgUK]

This is the off topic section

[malakai]

The method your choosing is only going to find true once therefore it only subtracts 1. A VBA script would be better but off hand search range add up all that equal same value in column I.

This is giving me errors will look some more at work right now but: DUH The error is it omits adjacent cells lol

=G3-SUM(COUNTIF(C3:C10,I3))

COUNTIF adds up all that are the same as I3 so say it finds 10 then subtract 10 from 50 value of G3

I put -G3 at the end and got a negative number

SUM Add up the results of (Countif range=C3:C10 compare to I3)

This should also work
=G3-COUNTIF(C3:C10,I3)

[MorgUK]

The method your choosing is only going to find true once therefore it only subtracts 1. A VBA script would be better but off hand search range add up all that equal same value in column I.

This is giving me errors will look some more at work right now but: DUH The error is it omits adjacent cells lol

=G3-SUM(COUNTIF(C3:C10,I3))

COUNTIF adds up all that are the same as I3 so say it finds 10 then subtract 10 from 50 value of G3

I put -G3 at the end and got a negative number

SUM Add up the results of (Countif range=C3:C10 compare to I3)

This should also work
=G3-COUNTIF(C3:C10,I3)

This works perfectly thank you! Much appreciated!