Countifs with one parameter covering multiple columns

[DarrylBurge]

Hi

I am trying to collate data, and part of it is to work out if a particular department has not completed paperwork, and I want to prove that it is not down tot he number of instruments in use, but just bad working practice/"mistakes".

On the attached (work in progress) workbook, on the "Results by instrument quantity" sheet I want H6 to tell me if a department (F6 dropdown) has not completed ANY submitted paperwork, based on the instrument count (G6 dropdown).

I know I want it to count:

If the specified department (F6) appears in 'Tray List Details'!$E$3:$E$32, has the word "no" in any of the cells in 'Tray List Details'!$I$3:$M$32, AND where the instrument count is less than the quantity specified in G6

Please Login or Register  to view this content.

If I do the same formula on a SINGLE COLUMN and I also specify the instrument count, it returns the correct figure

Please Login or Register  to view this content.

So, I suppose what I am asking is:
Am I able to use a cell reference for the instrument quantity?
If I am unable to monitor all 5 columns, if I want to check each column individually - 1 error, 2 errors, 3 errors etc, am I able to use Data Validation in any way to select the parameters of my COUNTIFS?

[Glenn Kennedy]

You'll need SUMPRODUCT:

=SUMPRODUCT(('Tray List Details'!$E$3:$E$32=F6)*('Tray List Details'!$I$3:$M$32="no")*('Tray List Details'!$F$3:$F$32<G6))

[Glenn Kennedy]

I should have scolded your for your poor thread title... but I didn't see it before posting. So I've changed it for you.

Do remember, though:

Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google. Your chosen title would produce a squillion useless hits. You need to take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself.

Regards,

Glenn.

[DarrylBurge]

Hi Glenn

1. Apologies for the thread title. No great excuse, but it was the end of quite a long day.

2. Thank you for the excellent help with the formula. I have altered it slightly so that I can monitor which part of the paperwork was not completed correctly, rather than just if there was a generic fault, so I am now using

Please Login or Register  to view this content.

And am then using J, K, L and M respectively.

I am also looking at doing the same for the 1 - 5 faults on each sheet. I assumed that when looking to "Tray List Details $N" it will be looking for either "True" or 1/0, so in "Results by Instrument Quantity M6" I have tried the following formulas, but they all just return 0

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

or am I miles off target?

[Glenn Kennedy]

??? There are no 1s in column N; only TRUE and FALSE. Am I looking in the right place?

[DarrylBurge]

Hi Glenn

Yes, but wasn't sure if the formula

Please Login or Register  to view this content.

, which results in True/False posted 1/0 which excel then changed to true/false?

That is why I tried the formula with 1, 0 and true, but regardless of what I entered for the formula to look for in Column N it didn't return anything other than 0

[Glenn Kennedy]

No... it actually returns TRUE or FALSE.


Modify to:

=SUMPRODUCT(('Tray List Details'!$E$3:$E$32=F6)*('Tray List Details'!$N$3:$N$32=TRUE)*('Tray List Details'!$F$3:$F$32<G6))

NB... no "" round TRUE.

[DarrylBurge]

You are a gent and a scholar

thank you.