Solved

# SumProduct not working as expected

Posted on 2011-10-24
203 Views
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
Question by:avoorheis

LVL 50

Accepted Solution

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

Author Comment

I'm glad I'm not losing my mind.
0

Author Closing Comment

thanks
0

## Featured Post

### Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.