Sumproduct Problem

[stehornby]

Ok, I have used sumproduct before with a lot of success but today it is not working and I cannot work out why.

I have two columns I want to count in SHEET A

I want the sum product if column M= TRUE and column BB= Case Control

I am counting on a separate sheet so the forumula I have used is

=SUMPRODUCT(--(A!M2:M2500="TRUE"), --(A!BB2:BB2500="Case Control"))

It just returns a value of 0 which I know to be false.


I may have isolated the problem. The COUNTIF formula will count the number of TRUE in column M. It will not however count the number of Case Controls in Column BB. The actual sheet has been an export from an access database. When I go into the cell of Column BB, there is a apostrophe before each 'Case Control or 'Cohort. However when I add this apostrophe to the formula I get an error. I have tried finding and replace to delete the apostrophe but it is not found by excel.

There are some blank cells in the BB column but that should not make difference should it?

Been at this for hours and getting really frustrated, help would be really appreciated.

Cheers

Steve

[DBY]

Hello
Have you tried removing the Quotation marks from around the TRUE criteria. For example, this works for me:

=SUMPRODUCT(($A$1:$A$10=TRUE)*($B$1:$B$10="Case Control"))

Whereas:

=SUMPRODUCT(($A$1:$A$10="TRUE")*($B$1:$B$10="Case Control"))

Does not.

DBY

[stehornby]

No I am afraid that does not work either.

[stehornby]

The formula is not showing an error it is just counting as 0 when I know that it is not.

Really aggravating.

[Cutter]

Have you checked your text values for leading/trailing spaces?
Do a COUNTIF(A!BB2:BB2500,"Case Control") and a COUNTIF(A!M2:M2500,"TRUE") - Do the results seem OK?

[stehornby]

I did a counted and the column m is counting but column BB is not.

Interestingly though column BB is counted in a pivot table.

Report due tomorrow. Urgent help required.

There are other ways of doing it but I will be up til 4am and even then if we have to a add more records in the future it will all have to be done again. Really need to get the sum product working.

Would it help to upload the file?

S

[Cutter]

Sure, but you can check it yourself. If "Case Control" is not being counted then there is a problem with those entries. Locate one of them and check to see if it has leading/trailing spaces. Do a COUNTIF(A!BB2:BB2500,"Case Control") on a shortened range where you can actually see results that should be counted. Once you find one that isn't being counted you can copy it, then select the entire range in B column, do Ctrl+H, paste the copied value in the Find field, type the proper value in the Replace Field, then click Replace All.

[daddylonglegs]

Is it sufficient to count cells that contain the text "Case Control" (possibly amongst other text)? If so try

=SUMPRODUCT((A!M2:M2500=TRUE)*ISNUMBER(SEARCH("Case Control",A!BB2:BB2500)))

[stehornby]

That last one is working. Thank you very much.

Lifesaver

S

[Cutter]

But you still have data that isn't what you think it is.