Count formula - or not?

Linda Duveen

Hi,

I have 20 columns that have a drop down list with three items that one must select "NO", "On-Site" and "Virtual". I need to be able to have two calculations come from those 20 columns for each row. The first calculation i need is "How many were either On-Site or Virtual (or how many were not "No") and the second calculation needs to be How many were "Virtual" . it's complicated as it will involve at least 20 cells in the row but i can't figure out how to do the formula to get the information i need. The sheet as about 100 rows that this will apply to.

thank you

Linda

Paul Newcome

Are all of the columns right next to each other, or are there other columns to exclude tucked in?

Linda Duveen

there is a column in between each one of those 20. basically, i am have a column that asks for the attendees name and then a column next to it that has that drop downmenu so i know if they were there in person, not at all or virtually. we allow up to 20 attendees ..

Paul Newcome

In that case you would want...

=COUNTIFS([1st Column]@row:[Last Column]@row, OR(@cell = "On-Site", @cell ="Virtual"))

=COUNTIFS([1st Column]@row:[Last Column]@row, @cell = "Virtual")

Linda Duveen

thank you! do i have to specify every cell in the row that it has to look at or is the : you have in the formula going to know that?

Paul Newcome

Basically you are setting the range to every column from the first one to the last one including the name columns. That is why we specify to count how many are equal to On-Site or Virtual. I highly doubt someone's name is going to be either of those. If we had counted across all columns and just said to count those that are not "No" then the name columns (and blank columns) would have been picked up in the count.

Linda Duveen

That makes sense! thank you so much! i will try that later today!

Paul Newcome

Happy to help. 👍️

Linda Duveen

Hi Paul, it's not working and i don't now what i am doing wrong. i added the formula to the cell in that row but it's not working. i do have to say that i have a ton of other columns in that row so i need it to select only a range of cells in that row to pick out the two words. can you help?

Linda Duveen

i dont know how to enter the name of the first column unless i have to enter the name manually instead of clicking on the cell?

Paul Newcome

First you will need to remove the cell reference before the COUNTIFS. Then you will need to update the column names in the formula to match the column names in your sheet.

Linda Duveen

i got a step further but it is still telling me that the formula is unpare... not working :)

Paul Newcome

Right. You need to remove that cell reference before the COUNTIFS. Getting rid of [Total Number Auditees Attended]8 at the very beginning should clear it up for you.