Are you sure that there aren't any errors in the data? that could be the problem. Also, the SUMPRODUCT function shouldn't be an array function so no {}'s.
This is a discussion on Countif/Sumproduct/Sumif all returning 0 within the Excel Questions forums, part of the Question Forums category; Hello all ... this is one of those "I'm going to throw my PC out the window in a minute ...
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!!
Are you sure that there aren't any errors in the data? that could be the problem. Also, the SUMPRODUCT function shouldn't be an array function so no {}'s.
The first thing I saw is I don't think you need the curly brackets for the sumproduct version, though I could be wrong.
Look for #VALUE or #N/A errors. COUNTIF will ignore these types of errors.
You say you get a zero result, so that probably rules out #VALUE! or #N/A as those would give an error not zero.
Are you sure that zero isn't a legitimate result? If there are only 2 matches for U76 then the formula can only return 2 at the most. Do you have any rows where both conditions are satisfied?
Another possibility is that column L is text formatted. COUNTIF will count both text and numeric values even if the criteria is numeric, SUMPRODUCT won't. Does this give you a value other than zero?
=SUMPRODUCT(--([CCBUSE.xls]Sheet1!$L$2:$L$2000=U76&""),--([CCBUSE.xls]Sheet1!$G$2:$G$2000=$X$74))
Cool bananas - thanks again!
Like this thread? Share it with others