Hi,

I wish to calculate the number of weekdays a project is recorded
against. In my list I can have multiple of the same weekday for a
specific project and task but I wish excel to only calculate it as 1
day for example:

Mon 1-Dec-08 Artwork Draw
Mon 1-Dec-08 Artwork Paint
Tue 2-Dec-08
Wed 3-Dec-08 Artwork Paint

Artwork was actually completed over 2 days not 3 how do I get excel to
calculate 2 days and not 3?

Thanks soo much!

wrote:
Hi,

I wish to calculate the number of weekdays a project is recorded
against. In my list I can have multiple of the same weekday for a
specific project and task but I wish excel to only calculate it as 1
day for example:

Mon 1-Dec-08 Artwork Draw
Mon 1-Dec-08 Artwork Paint
Tue 2-Dec-08
Wed 3-Dec-08 Artwork Paint

Artwork was actually completed over 2 days not 3 how do I get excel to
calculate 2 days and not 3?

Thanks soo much!

This array-entered* formula will do it:
=SUM(IF(C1:C4="Artwork",1/COUNTIF(B1:B4,B1:B4)))

*commit array formulae with Ctrl+Shift+Enter, not just Enter.

Much more on counting distinct/unique values can be found he
http://www.mrexcel.com/forum/showthread.php?t=70835

On Sat, 20 Dec 2008 21:03:09 -0500, smartin wrote:

wrote:
Hi,

I wish to calculate the number of weekdays a project is recorded
against. In my list I can have multiple of the same weekday for a
specific project and task but I wish excel to only calculate it as 1
day for example:

Mon 1-Dec-08 Artwork Draw
Mon 1-Dec-08 Artwork Paint
Tue 2-Dec-08
Wed 3-Dec-08 Artwork Paint

Artwork was actually completed over 2 days not 3 how do I get excel to
calculate 2 days and not 3?

Thanks soo much!

This array-entered* formula will do it:
=SUM(IF(C1:C4="Artwork",1/COUNTIF(B1:B4,B1:B4)))

*commit array formulae with Ctrl+Shift+Enter, not just Enter.

Much more on counting distinct/unique values can be found he
http://www.mrexcel.com/forum/showthread.php?t=70835

With a slight change in the data, your formula returns what I think is an
incorrect result:

Monday, December 01, 2008 Artwork Draw
Monday, December 01, 2008 Construction Paint
Tuesday, December 02, 2008
Wednesday, December 03, 2008 Artwork Paint

Your formula, with this data, should still return 2, if I understand the
question. However, it returns 1.5
--ron

Ron Rosenfeld wrote:
On Sat, 20 Dec 2008 21:03:09 -0500, smartin wrote:

wrote:
Hi,

I wish to calculate the number of weekdays a project is recorded
against. In my list I can have multiple of the same weekday for a
specific project and task but I wish excel to only calculate it as 1
day for example:

Mon 1-Dec-08 Artwork Draw
Mon 1-Dec-08 Artwork Paint
Tue 2-Dec-08
Wed 3-Dec-08 Artwork Paint

Artwork was actually completed over 2 days not 3 how do I get excel to
calculate 2 days and not 3?

Thanks soo much!
This array-entered* formula will do it:
=SUM(IF(C1:C4="Artwork",1/COUNTIF(B1:B4,B1:B4)))

*commit array formulae with Ctrl+Shift+Enter, not just Enter.

Much more on counting distinct/unique values can be found he
http://www.mrexcel.com/forum/showthread.php?t=70835

With a slight change in the data, your formula returns what I think is an
incorrect result:

Monday, December 01, 2008 Artwork Draw
Monday, December 01, 2008 Construction Paint
Tuesday, December 02, 2008
Wednesday, December 03, 2008 Artwork Paint

Your formula, with this data, should still return 2, if I understand the
question. However, it returns 1.5
--ron

Ah, right you are.

Here's a work-around, though I was hoping to avoid a helper formula.
Maybe there's a better way?

With a helper formula in column E where E1=B1&C1 this seems to sort it out:

=SUM(IF($C1:$C4="Artwork",1/COUNTIF(E1:E4,E1:E4)))

Thanks for pointing that out.

To count the unique dates (or numbers) that meet a condition (correspond to
artwork):

Array entered** :

=COUNT(1/FREQUENCY(IF(C1:C4="artwork",B1:B4),B1:B4))

=SUM(--(FREQUENCY(IF(C1:C4="artwork",B1:B4),B1:B4)0))

=SUM(IF(FREQUENCY(IF(C1:C4="artwork",B1:B4),B1:B4) ,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Ron Rosenfeld wrote:
On Sat, 20 Dec 2008 21:03:09 -0500, smartin wrote:

wrote:
Hi,

I wish to calculate the number of weekdays a project is recorded
against. In my list I can have multiple of the same weekday for a
specific project and task but I wish excel to only calculate it as 1
day for example:

Mon 1-Dec-08 Artwork Draw
Mon 1-Dec-08 Artwork Paint
Tue 2-Dec-08
Wed 3-Dec-08 Artwork Paint

Artwork was actually completed over 2 days not 3 how do I get excel to
calculate 2 days and not 3?

Thanks soo much!
This array-entered* formula will do it:
=SUM(IF(C1:C4="Artwork",1/COUNTIF(B1:B4,B1:B4)))

*commit array formulae with Ctrl+Shift+Enter, not just Enter.

Much more on counting distinct/unique values can be found he
http://www.mrexcel.com/forum/showthread.php?t=70835

With a slight change in the data, your formula returns what I think is an
incorrect result:

Monday, December 01, 2008 Artwork Draw
Monday, December 01, 2008 Construction Paint
Tuesday, December 02, 2008 Wednesday, December 03, 2008 Artwork Paint

Your formula, with this data, should still return 2, if I understand the
question. However, it returns 1.5
--ron

Ah, right you are.

Here's a work-around, though I was hoping to avoid a helper formula. Maybe
there's a better way?

With a helper formula in column E where E1=B1&C1 this seems to sort it
out:

=SUM(IF($C1:$C4="Artwork",1/COUNTIF(E1:E4,E1:E4)))

Thanks for pointing that out.

On Dec 21, 2:04*pm, "T. Valko" wrote:
To count the unique dates (or numbers) that meet a condition (correspond to
artwork):

Array entered** :

=COUNT(1/FREQUENCY(IF(C1:C4="artwork",B1:B4),B1:B4))

=SUM(--(FREQUENCY(IF(C1:C4="artwork",B1:B4),B1:B4)0))

=SUM(IF(FREQUENCY(IF(C1:C4="artwork",B1:B4),B1:B4) ,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"smartin" wrote in message

...

Ron Rosenfeld wrote:
On Sat, 20 Dec 2008 21:03:09 -0500, smartin wrote:

wrote:
Hi,

I wish to calculate the number of weekdays a project is recorded
against. In my list I can have multiple of the same weekday for a
specific project and task but I wish excel to only calculate it as 1
day for example:

Mon * *1-Dec-08 * * Artwork * * Draw
Mon * *1-Dec-08 * * Artwork * * Paint
Tue * * 2-Dec-08
Wed * 3-Dec-08 * * Artwork * * *Paint

Artwork was actually completed over 2 days not 3 how do I get excel to
calculate 2 days and not 3?

Thanks soo much!
This array-entered* formula will do it:
=SUM(IF(C1:C4="Artwork",1/COUNTIF(B1:B4,B1:B4)))

*commit array formulae with Ctrl+Shift+Enter, not just Enter.

Much more on counting distinct/unique values can be found he
http://www.mrexcel.com/forum/showthread.php?t=70835

With a slight change in the data, your formula returns what I think is an
incorrect result:

Monday, December 01, 2008 Artwork Draw
Monday, December 01, 2008 Construction Paint
Tuesday, December 02, 2008 Wednesday, December 03, 2008 Artwork Paint

Your formula, with this data, should still return 2, if I understand the
question. *However, it returns 1.5
--ron

Ah, right you are.

Here's a work-around, though I was hoping to avoid a helper formula. Maybe
there's a better way?

With a helper formula in column E where E1=B1&C1 this seems to sort it
out:

=SUM(IF($C1:$C4="Artwork",1/COUNTIF(E1:E4,E1:E4)))

Thanks for pointing that out.

Thanks everyone for giving me solutions! I used Biff's COUNT formula
and it worked!