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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,527
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,075
Messages
5,570,051
Members
412,309
Latest member
essobsan
Top