Sumproduct with Countifs and data validation

bradrz_2000

New Member
Joined
Jun 6, 2009
Messages
5
I am trying to count on a number of criteria, however, the result keeps coming back as zero, and I have no idea why. It works when I use values, but not when I reference a cell, and I am very confused by this. Here is the formula:

=SUMPRODUCT(COUNTIFS(Database!E:E,3,$P:$P,{"Below"},D:D,"Male"))

This works fine and does return the correct numbers, however I need to be able to change the values I would like to count.

So when I try and change it to:

=SUMPRODUCT(COUNTIFS(Database!E:E,=L3,$P:$P,{"Below"},D:D,"=D4"))

It returns zero, despite 3 and Male being in those cells.

What very simple thing am I doing wrong.

Thank you for the help
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=COUNTIFS(Database!E:E,L3,$P:$P,"Below",D:D,D4)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,394
Members
412,386
Latest member
Yasaman
Top