Calculate the number of weeksdays based on a cell value

[roniaelm]

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!

 

[Ron Rosenfeld]

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 here:
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

 

[T. Valko]

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)

 

[roniaelm]

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)

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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)

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

 

[roniaelm]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP





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!

 

[T. Valko]

If I understand what you want....

Still array entered** :

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

=COUNT(1/FREQUENCY(IF((C1:C4="artwork")*(D14="draw"),B1:B4),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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP





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!

 

[roniaelm]

If I understand what you want....

Still array entered** :

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

=COUNT(1/FREQUENCY(IF((C1:C4="artwork")*(D14="draw"),B1:B4),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






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!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

If I understand what you want....

Still array entered** :

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

=COUNT(1/FREQUENCY(IF((C1:C4="artwork")*(D14="draw"),B1:B4),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






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!