Creating an array to find months

[ssrvant]

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 th
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 mont
of june, and then count how many "Absences" are assocated with each da
in june.

I hope that this makes sense..

ssrvan

 

[Franz Verga]

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

 

[Bob Phillips]

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

--

HTH

Bob Phillips

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

 

[ssrvant]

Franz,

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

ssrvan

 

[Franz Verga]

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