And/Countif formula
I have a range of data which looks like something like this:-
london -2 london -2 london -1 wales -2 I am trying to create a formula that counts the number of times the first column contains -1 and the second column contains -2, so far I have come up with the formula:- =SUM(IF(AND($B$4:$B$11="london",$C$4:$C$11=-2),1,0)) But this only returns a value of 1, can you help? |
And/Countif formula
Hi
This needs to be entered as an array formula so commit with Ctrl+Shift+Enter. Excel will include the curly braces { } when you do this. Do not type them yourself. {=SUM(IF(AND($B$4:$B$11="london",$C$4:$C$11=-2),1,0))} for a non-array solution you could use =SUMPRODUCT(--($B$4:$B$11="london"),--($C$4:$C$11=-2)) -- Regards Roger Govier "nir020" wrote in message ... I have a range of data which looks like something like this:- london -2 london -2 london -1 wales -2 I am trying to create a formula that counts the number of times the first column contains -1 and the second column contains -2, so far I have come up with the formula:- =SUM(IF(AND($B$4:$B$11="london",$C$4:$C$11=-2),1,0)) But this only returns a value of 1, can you help? |
All times are GMT +1. The time now is 09:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com