Sumproduct with TRUE cell entries

I am trying to count all instances where A2:A92="TRUE" AND E2:E92="INSIDER." I only want to count the instances where both fields match.

I used the following sumproduct:
=SUMPRODUCT(('CASE DATA'!A2:A92="TRUE")*('CASE DATA'!E2:E92="INSIDER"))

However, it keeps returning "0", when I know this is not the case. Any ideas?

Later, I need to count all instances where A2:A92="TRUE," E2:E92="INSIDER," AND H2:H92="ARCHIVE" OR H2:H92="HISTORICAL SOCIETY".

Help?

Thanks,
Brittany

Is TRUE really text and not just the TRUE/FALSE constants.

=SUMPRODUCT(('Case data'!A2:A92=TRUE)*('Case data'!E2:E92="INSIDER"))

If the TRUE/FALSE is result of boolean formula.. then don't put quotes around the TRUE or leave out the =TRUE altogether.

=SUMPRODUCT(('CASE DATA'!A2:A92=TRUE)*('CASE DATA'!E2:E92="INSIDER"))
or
=SUMPRODUCT(('CASE DATA'!A2:A92)*('CASE DATA'!E2:E92="INSIDER"))

the second formula:

=SUMPRODUCT(('CASE DATA'!A2:A92)*('CASE DATA'!E2:E92="INSIDER")*(('CASE DATA'!H2:H92="ARCHIVE")+('CASE DATA'!H2:H92="HISTORICAL SOCIETY")))

if you just typed in TRUE then it recognizes it as the Boolean.

You would need to remove the quotes.

If you typed an apostrophe before it, or in a cell preformatted as text, then it would be text.

It kind of works like numbers in this case...

But to be safer, maybe it is better to change the word..

Do you mean?

=Sumproduct((A2:A92 = "KNOWN")*(N2:N92>=18))

and

=Sumproduct((A2:A92 = "KNOWN")*(N2:N92>=18)*(N2:N92<=30))

The first one counts how many times range A has Known while at the same row in range N you have >=18.

Similary, the second does the same but checks if range is between 18 and 30... if you don't want to include 18 and 30 in second one, take out the equal signs, leaving just > and <, respectively.

You can always post a workbook sample so we can see what is going wrong.

In the Case Data... why are you saying that the results should be 1 and 11? I manually count 50 and 14.

And so getting 54 and 14 was close, but I see you also have text in column N... that is causing some trouble, so should use:

=SUMPRODUCT((A2:A92="KNOWN")*(N2:N92>=18)*ISNUMBER(N2:N92))

and

=SUMPRODUCT((A2:A92 = "KNOWN")*(N2:N92>=18)*(N2:N92<=30)*ISNUMBER(N2:N92))