Countif/Sumproduct/Sumif all returning 0

[Macrobiotic Brown]

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!!

[Seti]

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.

[Weaver]

The first thing I saw is I don't think you need the curly brackets for the sumproduct version, though I could be wrong.

[Macrobiotic Brown]

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.

Hi Seti - what types of errors would I be looking for? I thought that by getting a correct result from the first two COUNTIF formulas that I could rule out any data errors.

Removing the {} from the SUMPRODUCT formula didn't produce the right result either.

[Seti]

Look for #VALUE or #N/A errors. COUNTIF will ignore these types of errors.

[barry houdini]

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))

[Macrobiotic Brown]

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))

Hooray! This formula is giving me the right results. Thanks Barry!

How did the &"" bit change the result though? Is that a formatting change?

[barry houdini]

How did the &"" bit change the result though? Is that a formatting change?

Yes, concatenating the number in U76 with "" converts it to a text value. Long term it might be best to convert column L to numbers and use your original formula

To convert column L try this

Select that column and use

Data > Text to columns > Finish

[Macrobiotic Brown]

Cool bananas - thanks again!