Calculate the number of weeksdays based on a cell value
 

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!

Calculate the number of weeksdays based on a cell value
 

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

Calculate the number of weeksdays based on a cell value
 

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

Calculate the number of weeksdays based on a cell value
 

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.

Calculate the number of weeksdays based on a cell value
 

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.

Calculate the number of weeksdays based on a cell value
 

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!

Calculate the number of weeksdays based on a cell value
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Calculate the number of weeksdays based on a cell value
 

On Dec 25 2008, 1:18*pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

wrote in message

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

m...

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!

Can I get assistance with this code again. i have tried but I am not
sure fow to amend the formula so that it sums the number of days based
on two critiera, i.e. Artwork and Draw will be calculated as 1 day and
Artwork and Paint will be calculated as 2 days?

Thanks again for your help!

Calculate the number of weeksdays based on a cell value
 

If I understand what you want....

Still array entered** :

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

=COUNT(1/FREQUENCY(IF((C1:C4="artwork")*(D1:D4="draw"),B1:B 4),B1:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


wrote in message
...
On Dec 25 2008, 1:18 pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

wrote in message

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

m...

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!

Can I get assistance with this code again. i have tried but I am not
sure fow to amend the formula so that it sums the number of days based
on two critiera, i.e. Artwork and Draw will be calculated as 1 day and
Artwork and Paint will be calculated as 2 days?

Thanks again for your help!

Calculate the number of weeksdays based on a cell value
 

On Jan 8, 5:49*pm, "T. Valko" wrote:
If I understand what you want....

Still array entered** :

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

=COUNT(1/FREQUENCY(IF((C1:C4="artwork")*(D1:D4="draw"),B1:B 4),B1:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

wrote in message

...
On Dec 25 2008, 1:18 pm, "T. Valko" wrote:



You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

wrote in message

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

m...

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!

Can I get assistance with this code again. i have tried but I am not
sure fow to amend the formula so that it sums the number of days based
on two critiera, i.e. Artwork and Draw will be calculated as 1 day and
Artwork and Paint will be calculated as 2 days?

Thanks again for your help!

Hi Biff,

Thanks again for your help! That worked!

Regards!

Calculate the number of weeksdays based on a cell value
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
On Jan 8, 5:49 pm, "T. Valko" wrote:
If I understand what you want....

Still array entered** :

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

=COUNT(1/FREQUENCY(IF((C1:C4="artwork")*(D1:D4="draw"),B1:B 4),B1:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

wrote in message

...
On Dec 25 2008, 1:18 pm, "T. Valko" wrote:



You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

wrote in message

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

m...

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!

Can I get assistance with this code again. i have tried but I am not
sure fow to amend the formula so that it sums the number of days based
on two critiera, i.e. Artwork and Draw will be calculated as 1 day and
Artwork and Paint will be calculated as 2 days?

Thanks again for your help!

Hi Biff,

Thanks again for your help! That worked!

Regards!