Sumproduct(?)

[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

 

[T. Valko]

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).

 

[Pete_UK]

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

 

[Guest]

Thank you very much T. Valko, i wasn't aware of the column range limitation.
Appreciate it. Enjoy the day.

 

[T. Valko]

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

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Guest]

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.

 

[Pete_UK]

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