count conditional days - ajit
 

I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power

=SUMPRODUCT(--(A3:A10="Jack"),--(OFFSET(A3:A10,,1)="P"))

Change the name to suit, the A/P to suit, and the ,,1 to reflect the day

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ajit Munj" wrote in message
...
I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power

With the days in row 1 and P/A in row 3, try:

=SUMPRODUCT(--(1:1="Sun"),--(3:3="P"))

Duplicate the formula for rest of the days, and then
repeat for "A".

HTH
Jason
Atlanta, GA

-----Original Message-----
I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power
.

Bob,

Think I'm missing something here - what's the "A/P"?

Pete Rooney



"Bob Phillips" wrote:

=SUMPRODUCT(--(A3:A10="Jack"),--(OFFSET(A3:A10,,1)="P"))

Change the name to suit, the A/P to suit, and the ,,1 to reflect the day

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ajit Munj" wrote in message
...
I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power

Bob,
Ever wished the earth could open up and swallow you? "Absent/Presnt" - Doh!
Sorry to hijack this string with abject stupidity.

Pete



"Peter Rooney" wrote:

Bob,

Think I'm missing something here - what's the "A/P"?

Pete Rooney



"Bob Phillips" wrote:

=SUMPRODUCT(--(A3:A10="Jack"),--(OFFSET(A3:A10,,1)="P"))

Change the name to suit, the A/P to suit, and the ,,1 to reflect the day

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ajit Munj" wrote in message
...
I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power

Thanks Bob,want some explanations
1. what is the use of "--" in formula?
2. What is offset function? Help on this functions is not available
with my excel version.
3. This query is not related with the given formula. How can I
use "and", "or" expression as it is used in dbase or foxpro (e.g.
if
(this conditions is true) .and.
(that conditions is true)
then
(do this)
..or.(do this)

"Bob Phillips" wrote:

=SUMPRODUCT(--(A3:A10="Jack"),--(OFFSET(A3:A10,,1)="P"))

Change the name to suit, the A/P to suit, and the ,,1 to reflect the day

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ajit Munj" wrote in message
...
I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power

"Ajit Munj" wrote in message
...
Thanks Bob,want some explanations
1. what is the use of "--" in formula?

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

2. What is offset function? Help on this functions is not available
with my excel version.

The OFFSET is used to get a row or column displaced from the source. In this
case, as you sometimes want to count Sun totals, sometimes Mon, etc, I am
using the OFFSET to dynamically get that day amount, OFFSET from column A.
So on Sun (1), it gets column B amounts it gets column C amounts, etc.

3. This query is not related with the given formula. How can I
use "and", "or" expression as it is used in dbase or foxpro (e.g.
if
(this conditions is true) .and.
(that conditions is true)
then
(do this)
.or.(do this)

You can't. SUMPRODUCT is an Excel formula, and this usage is peculiar to
Excel. Each conditional test is effectively an AND condition.

Bob Phillips" wrote:

=SUMPRODUCT(--(A3:A10="Jack"),--(OFFSET(A3:A10,,1)="P"))

Change the name to suit, the A/P to suit, and the ,,1 to reflect the day

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ajit Munj" wrote in message
...
I have created data as below:
A B C D E F
Sun Mon Tue Wed Thu Fri Sat....
1 2 3 4 5 6 7 .....31
Jack P P A P A P A P

I want to calculate how many Sundays, Tuesdays etc. Jack
was present and absent? How can I do this? (P=Present
A=absent)
Ajit
--
Knowldege is Power