Ignore blank cells in formula counting number of different cells.

[deneh]

Hi,

I have a formula for counting the number of bins in a column of cells where another criteria applies. There may not always be a bin number in that column, unfortunately this results in an #N/A error.

Can anyone help with a revision to this formula to ignore blank cells?

Example sheet attached.

Thanks in advanceBinCount.xlsx

[Ali Kırksekiz]

Please Login or Register  to view this content.

ctrl+shift+enter

[Glenn Kennedy]

If you are counting the number of non-zero cells in column B that meet the criteria in D, use this: =COUNTIFS(B5:B11,">0",E5:E11,D1) and copy paste into second grouping.

If not, please explain why you think the result to the first one is 4.

[deneh]

Hi Ali, Sorry your formula doesnt appear to work.

Glen. I am only counting cells which have a value and not blank. In the first example it returns 4 because in column 'Del' there are 4 #2 each having a corresponding number in 'Bin' column which is not repeated where they also have #2 in Del column.

Hope this makes sense.

[Ali Kırksekiz]

Check file

[Glenn Kennedy]

It does - so the formula above will work fine. see attached.

[deneh]

Sorry, I don't think you are getting what I am trying to achieve. I'll try and explain again.

Bin column: This is a bif reference number and can appear more than once.
Del Column: This is a delivery reference and Bins can be assigned to that delivery.
Not every line will have a Bin number.

Where the Del number is equal to that specified in the Load Cell, I then want to count the number of bins in the Bin column that relate to that Del number - BUT where a bin number is repeated 2, 3 or more times I only want to count that bin once - That part all works with my origional formula. What my problem is; is if the Bin column has a blank cell (as it's an item that doesnt have a Bin reference) I get an #N/A error returnded.

I was therefore looking for some assistance to modify my formula to ignore the blank cells and still count the bins as the above/origional formula.

BinCount2.xlsx

[Glenn Kennedy]

OK. In that case, why doesn't Ali's formula do what you want? (Did you array enter it??)