Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF FORMULA ISSUES - NEED FORMULA CORRECTED | Excel Worksheet Functions | |||
Formula Help (countif, I think....) | Excel Discussion (Misc queries) | |||
CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
Help with CountIF formula | Excel Programming | |||
Another Countif Formula | Excel Discussion (Misc queries) |