Sumproduct(?)
 

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

Sumproduct(?)
 

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

--
Biff
Microsoft Excel MVP


"Suzanne" wrote in message
...
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

Sumproduct(?)
 

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

On Jul 11, 6:30 pm, Suzanne wrote:
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

Sumproduct(?)
 

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Suzanne" wrote in message
...
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

Sumproduct(?)
 

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

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


--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
ups.com...
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

On Jul 11, 6:30 pm, Suzanne wrote:
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

Sumproduct(?)
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Suzanne" wrote in message
...
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

Sumproduct(?)
 

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" wrote:

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

On Jul 11, 6:30 pm, Suzanne wrote:
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

Sumproduct(?)
 

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

On Jul 11, 7:24 pm, Suzanne wrote:
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" wrote:
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

On Jul 11, 6:30 pm, Suzanne wrote:
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 - Hide quoted text -

- Show quoted text -