Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function query for multiple colums
How do I count the number of occurrences of "1" in the A column each time
there is a negative value in the J column on the same row. i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want this cell to be counted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function query for multiple colums
Try
=SUMPRODUCT(--(A1:A20=1),--(J1:J20<0)) Regards, Alan. "Malcolm Gordon" <Malcolm wrote in message ... How do I count the number of occurrences of "1" in the A column each time there is a negative value in the J column on the same row. i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want this cell to be counted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function query for multiple colums
Hi Alan,
Thanks for the response. Unfortunately, it didn't work. I should add that the value s in Column J are based on formulas, does this present a problem? "Alan" wrote: Try =SUMPRODUCT(--(A1:A20=1),--(J1:J20<0)) Regards, Alan. "Malcolm Gordon" <Malcolm wrote in message ... How do I count the number of occurrences of "1" in the A column each time there is a negative value in the J column on the same row. i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want this cell to be counted. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function query for multiple colums
Hi Alan,
I changed the ordering and your formula works! Thanks you very much :-) "Malcolm Gordon" wrote: Hi Alan, Thanks for the response. Unfortunately, it didn't work. I should add that the value s in Column J are based on formulas, does this present a problem? "Alan" wrote: Try =SUMPRODUCT(--(A1:A20=1),--(J1:J20<0)) Regards, Alan. "Malcolm Gordon" <Malcolm wrote in message ... How do I count the number of occurrences of "1" in the A column each time there is a negative value in the J column on the same row. i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want this cell to be counted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function query for multiple colums
Hi Malcolm,
Glad it works for you, but it shouldn't make any difference whether the value in a cell is a number you've entered or the result of a formula, it's either a negative value or it isn't. I'm not sure what you mean by 'changing the ordering' though! Regards, Alan. "Malcolm Gordon" wrote in message ... Hi Alan, I changed the ordering and your formula works! Thanks you very much :-) "Malcolm Gordon" wrote: Hi Alan, Thanks for the response. Unfortunately, it didn't work. I should add that the value s in Column J are based on formulas, does this present a problem? "Alan" wrote: Try =SUMPRODUCT(--(A1:A20=1),--(J1:J20<0)) Regards, Alan. "Malcolm Gordon" <Malcolm wrote in message ... How do I count the number of occurrences of "1" in the A column each time there is a negative value in the J column on the same row. i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want this cell to be counted. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function query for multiple colums
Hi,
Try the following array formula (Ctrl+Shift+Enter) SUM(IF((A3:A7=1)*(J3:J7<0),1,0)) Regards, "Malcolm Gordon" wrote: How do I count the number of occurrences of "1" in the A column each time there is a negative value in the J column on the same row. i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want this cell to be counted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
"Average" function query | Excel Worksheet Functions | |||
How do you ignore hidden rows in a countif() function | Excel Worksheet Functions | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) |