Sumproduct(?)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I want to count the dates in column B where the corresponding value
in column A is 'Sched'. Expecting the answer to be: 2 for the example below.
I've tried to use and array (which i rarely use), i've also looked into
SUMPRODUCT (which i've never used). I haven't had any luck with either. Can
anyone help please?

A B
1 Sched 2/15/2007
2 2/19/2007
3 Sched 3/1/2007
4 Sched
5
 
Try this:

=SUMPRODUCT(--(A1:A10="Sched"),--(ISNUMBER(B1:B10)))

Note: you can't use entire columns as range references (unless you're using
Excel 2007).
 
If you want an array* formula, try this:

=SUM(IF((A1:A5="Sched")*(B1:B5<>""),1)

Strictly speaking, it only tests for cells in column B not being
empty, not specifically for them containing a date (which is only a
number to Excel).

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must commit it with CTRL-SHIFT-ENTER rather
than the usual ENTER. If you do this correctly, then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

An alternative would be:

=SUMPRODUCT((A1:A5="Sched")*(B1:B5<>""))

Hope this helps.

Pete
 
Thank you very much T. Valko, i wasn't aware of the column range limitation.
Appreciate it. Enjoy the day.
 
Thank you, as i'm turning the spreadsheet over to a less experienced group,
was trying to stear clear of an array. Not that the one i tried to use
worked : ) I appreciate one that does and will save for future use. Cheers.
 
Thanks for the feedback. Now that Biff has shown you an alternative,
you can see the similarity between it and the SUMPRODUCT version -
some prefer the latter because you don't have to use CSE to commit.

Pete
 
Back
Top