Macrobiotic Brown
Board Regular
- Joined
- Nov 22, 2007
- Messages
- 58
Hello all ... this is one of those "I'm going to throw my PC out the window in a minute if it doesn't do what I want it to" problems ... so I'm hoping I haven't missed anything too obvious.
I'm trying to count the occurences of conditions in two columns.
=COUNTIF([CCBUSE.xls]Sheet1!$G:$G,$X$74) returns a value of 540, and =COUNTIF([CCBUSE.xls]Sheet1!$L:$L,U76) returns a value of 2 - so that tells me that the formats etc. are working OK - but trying to combine the formula always produces a result of 0. I can confirm that there are definitely matches in the CCBUSE.xls workbook where both conditions are met in one row.
I've tried these:
{=(COUNT(IF(([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76)*([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74),[CCBUSE.xls]Sheet1!$L$2:$L$2000)))}
{=SUMPRODUCT(--([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76),--([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74))}
{=SUM(IF([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76,IF([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74,1,0)))}
Can someone please tell me what I've missed? If it makes a difference, X74 is a text field while U76 is a number field.
Thanks!!
I'm trying to count the occurences of conditions in two columns.
=COUNTIF([CCBUSE.xls]Sheet1!$G:$G,$X$74) returns a value of 540, and =COUNTIF([CCBUSE.xls]Sheet1!$L:$L,U76) returns a value of 2 - so that tells me that the formats etc. are working OK - but trying to combine the formula always produces a result of 0. I can confirm that there are definitely matches in the CCBUSE.xls workbook where both conditions are met in one row.
I've tried these:
{=(COUNT(IF(([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76)*([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74),[CCBUSE.xls]Sheet1!$L$2:$L$2000)))}
{=SUMPRODUCT(--([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76),--([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74))}
{=SUM(IF([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76,IF([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74,1,0)))}
Can someone please tell me what I've missed? If it makes a difference, X74 is a text field while U76 is a number field.
Thanks!!