Creating an array to find months
 

Can someone please show me a function that can produce the following.
I have 6 dates (2/06,3/09,4/07,3/06,7/06,1/06) where the format of the
date is "d/mm" (day/month). these dates are in cells A1:F1.
now below the dates in cells A2,D2,E2,and F2 is the word "Absent"

I need a formula that will give me only the days that are in the month
of june, and then count how many "Absences" are assocated with each day
in june.

I hope that this makes sense..

ssrvant


--
ssrvant
------------------------------------------------------------------------
ssrvant's Profile: http://www.excelforum.com/member.php...o&userid=35729
View this thread: http://www.excelforum.com/showthread...hreadid=555158

Creating an array to find months
 

Nel post
*ssrvant* ha scritto:

Can someone please show me a function that can produce the following.
I have 6 dates (2/06,3/09,4/07,3/06,7/06,1/06) where the format of the
date is "d/mm" (day/month). these dates are in cells A1:F1.
now below the dates in cells A2,D2,E2,and F2 is the word "Absent"

I need a formula that will give me only the days that are in the month
of june, and then count how many "Absences" are assocated with each
day in june.

I hope that this makes sense..

ssrvant

Hi Ssrvant,

knowing your expected results would be better...

Anyway, the formula:

=SUMPRODUCT(--(TEXT(A1:F1,"mm")="06"))

will give you the number of days that are in the month of June, while the
formula:

=SUMPRODUCT((TEXT(A1:F1,"mm")="06")*(A2:F2="Absent "))

will give you the number of days that are in the month of June and that have
the word "Absent" associated.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Creating an array to find months
 

=SUMPRODUCT(--(MONTH(A1:F2)=6),--(A2:F2="Absent"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ssrvant" wrote in
message ...

Can someone please show me a function that can produce the following.
I have 6 dates (2/06,3/09,4/07,3/06,7/06,1/06) where the format of the
date is "d/mm" (day/month). these dates are in cells A1:F1.
now below the dates in cells A2,D2,E2,and F2 is the word "Absent"

I need a formula that will give me only the days that are in the month
of june, and then count how many "Absences" are assocated with each day
in june.

I hope that this makes sense..

ssrvant


--
ssrvant
------------------------------------------------------------------------
ssrvant's Profile:
http://www.excelforum.com/member.php...o&userid=35729
View this thread: http://www.excelforum.com/showthread...hreadid=555158

Creating an array to find months
 

Franz,

It worked.. Thanks so much.. I really resarched a way to do this but
had great difficulty. By the way. what does the "*" do?

ssrvant


--
ssrvant
------------------------------------------------------------------------
ssrvant's Profile: http://www.excelforum.com/member.php...o&userid=35729
View this thread: http://www.excelforum.com/showthread...hreadid=555158

Creating an array to find months
 

Nel post
*ssrvant* ha scritto:

Franz,

It worked.. Thanks so much.. I really resarched a way to do this but

You're welcome.

had great difficulty. By the way. what does the "*" do?

the "*" is the AND operator.

Maybe you can find useful this page on Debra Dalgleish's site about the
SUMPRODUCT function:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy