• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SumProduct not working as expected

I thought I'd be able to count the number of row that matched the criteria below, but, when I manually filter, I get a different count. Can I not do this with sumproduct?
=SUMPRODUCT((Final!$I$2:$I$21291<>"100")*(Final!$M$2:$M$21291<>"Yes")*(Final!$AC$2:$AC$21291="Easy"))
So, I want to find how many rows will be left if I filter where columns are
I <> 100
M<> Yes
AC=Easy
Filtering manually I get fewer rows.
any ideas?
thanks
alan
0
avoorheis
Asked:
avoorheis
  • 2
1 Solution
 
barry houdiniCommented:
That looks like a valid formula but for numbers you would normally use 100 rather than "100" (unless column I is text-formatted) so try without quotes

=SUMPRODUCT((Final!$I$2:$I$21291<>100)*(Final!$M$2:$M$21291<>"Yes")*(Final!$AC$2:$AC$21291="Easy"))

regards, barry
0
 
avoorheisAuthor Commented:
I'm glad I'm not losing my mind.
0
 
avoorheisAuthor Commented:
thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now