Excel Formula help

NBTD

Hi there,

I have an Excel spreadsheet and want to look up the amount of numbers in a column if there is a certain value in a certain other column... (Sorry, hard to explain.

I want to do a COUNTA on values in column L and all following only if the cell in col B contains "Sample text".

Have tried as follows:

=IF($B:$B="Sample text",(COUNTA(QM$14:QM$213)-COUNTIF(QM$14:QM$213,"FOA")-COUNTIF(QM$14:QM$213,"FOP")-COUNTIF(QM$14:QM$213,"AL")-COUNTIF(QM$14:QM$213,"CL")-COUNTIF(QM$14:QM$213,"S")-COUNTIF(QM$14:QM$213,"T")),"")

but just returns 0

Thoie

All your CountAs and CountIfs are only looking at a relatively small range 14-213in a single column QM - not column L and above.

So to start with change QM$14:QM$213 to L$14:QM$213 to increase your range.

Do you deliberately want to subtract anything from that range that contains FOP, FOA, AL, CL, S, or T?

Thoie

Looking at it again, and trying to figure out what you're trying to do, a pivot table with filters might be the answer here.

Triangla

Use the countif function

=COUNTIF(range,"sample text")

NBTD

Thoie wrote: »

All your CountAs and CountIfs are only looking at a relatively small range 14-213in a single column QM - not column L and above.

So to start with change QM$14:QM$213 to L$14:QM$213 to increase your range.

Do you deliberately want to subtract anything from that range that contains FOP, FOA, AL, CL, S, or T?

Need to do it for each column (each column is a different date) And yes, need to subtract those exact cell values.

NBTD

Triangla wrote: »

Use the countif function

=COUNTIF(range,"sample text")

Countif can tell me the amount of occurrences of "Sample text" in column B, but I want to know out of those occurrences, how many have values in column QM?

Triangla

Can you do a vlookup between the columns to find common values and then a countif to count them?

Can you explain a little more what you're looking to do.

NBTD

sorry, not explaining myself very well

Its for a roster, see below. The numbers in columns D-F represent the days that someone is working on a 3-day roster (v basic example)

I want to find out how many people are staying in a hotel on a certain day, so if Col B contains hotel, then count no of values in Col D for 10 Dec. (In this case, 1)

Thoie

Definitely a pivot table in that case. See attached example - that took less than a minute to put together.

I've put the pivot table on the same sheet as the data, but obviously it might be more convenient to put it on a new sheet.