Help! COUNTIF and SUMPRODUCT are eating my brain!

[unionjack]

This *should* be easy, I guess, but I keep getting errors where I canno
see a problem with my formula (Tho' admittedly I'm by no means an Exce
expert). Here's my task:
I have column G labeled "Tasks" and the cell values are either "Open"
"Closed", "Cancelled", or blank. Then I have column L labeled "Name
and the cell values are, of course, names. I'm simply trying to come u
with a formula that will tally the tasks that are "Open" and of those
how many are assigned to "Smith".

After fruitless attempts using COUNTIF formulas, I've tried using

=SUMPRODUCT(COUNTIF(G:G,"Smith"))*(COUNTIF(L:L,"Open"))

I really thought I had it with the sumproduct formula, but I kee
getting a #NUM! error.

I will mention, too, that on these columns/cells, I'm using a dat
validation drop down pick list. ...and this is on Microsoft Excel 200
(9.0.6926 SP-3), btw...

Any help on this? Mucho appreciado

 

[mzehr]

Hi,
A couple of problems: Sumproduct cannot work with entire
columns. You need to specify the cell ranges within a
column. Next you don't need countif included in your
formula.

Try =sumproduct((G1:G1000="Smith")*(L1:L1000="Open"))

Bob Phillips has a great site for a fantastic overview of
sumproducts try
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Frank Kabel]

Hi
use:
=SUMPRODUCT(--(G1:G1000="Smith"),--(L1:L1000="Open"))

also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html