count conditional days - ajit

[Ajit Munj]

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

 

[Bob Phillips]

=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)

 

[Jason Morin]

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

 

[Peter Rooney]

Bob,

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

Pete Rooney

 

[Peter Rooney]

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

Pete

 

[Ajit Munj]

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]

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.