count conditional days - ajit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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)
 
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
 
Bob,
Ever wished the earth could open up and swallow you? "Absent/Presnt" - Doh!
Sorry to hijack this string with abject stupidity.

Pete
 
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)
 
Ajit Munj said:
Thanks Bob,want some explanations
1. what is the use of "--" in formula?
Click to expand...

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.
Click to expand...

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)
Click to expand...

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