count

[chungtinhlak]

{=COUNT(IF($E$48:$E$881>$G$7,IF($E$48:$E$881<=$H$7,IF($F$48:$F$881=$B29,$IV$48:$IV$881))))}

can that work? I'm taking over a workbook that someone has created and that that's what the formula shows.

when I open it, all the cell with formula has value in it, but if I click on it and press entered, the value will be one and the formula will no longer contains "{".

does column goes out that far, IV48, that's too far out of the range right?

the formula that I want goes something like this.

=if(and(E48>B6,E48<=E7,F48=B29)
but I want to count total that met this condidtion for the range from 48:883

can you guys help me.

I tried to digest the old formula but it just doesn't make sense.

Can and do =if(and(E48>B6,E48<=E7,F48=B29),1,"") and do this for all the rows and count the 1, but I have many columns that I have to do this on.

but it would really help me if there is a quicker way.

Thanks in advance.
thanks

attached is my sample worksheet, row 29 contains the old formula and starting from R48 dow and my formula, I want to combine those into 1 step and do it for all.

thanks

[Aussiebear]

There's a space between $IV$48 in your formula. Is that your typo?

[chungtinhlak]

yeah
that's my typo here, but in the formula it's correct.

[Mikey]

That's a pretty strange way of achieving a result [but still probably effective]

Column IV is the last column in Excel 2003 and earlier versions, that column is blank. When the three conditions are true the formula returns the value from that column - blanks are treated like zeroes so when COUNT counts the numbers returned it counts those zeroes, effectively giving a count of rows where the conditions are met

It would be more usual to do something like this, which wouldn't rely on keeping column IV blank

=COUNT(IF($E$48:$E$881>$G$7,IF($E$48:$E$881<=$H$7, IF($F$48:$F$881=$B29,1))))

....but, as you noted, that's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER to give the { and } around the formula. Probably simpler to use SUMPRODUCT which can just be entered normally, i.e.

=SUMPRODUCT(($E$48:$E$881>$G$7)*($E$48:$E$881<=$H$7)*($F$48:$F$881=$B29))