count if

[newbie_010108]

hi im using excel 2003. How would i count the number of "Lates" for each
individual. here is my data:
sheet1:
name Attendance
karen late
bryan late
karen late
bryan
ferdy

Sheet2:
name # of Lates
karen 2
bryan 1
ferdy 0

 

[Shane Devenshire]

Hi,

=SUMPRODUCT(--(Sheet1!A$2:A$20=A2),--(Sheet1!B$2:B$20="Late"))

Assuming titles on the first row in both sheets.

 

[T. Valko]

Try this:

Entered on sheet2 B2 then copied down as needed:

=SUMPRODUCT(--(Sheet1!A$2:A$10=A2),--(Sheet1!B$2:B$10="late"))

 

[newbie_010108]

hi shane,
it works! perfect! just curious.. why is it needs to be =sumproduct? and
what is -- for? and why is it need to have a dollar sign?.. i thought it
should be count if function....
your so great!
thanks!

 

[Shane Devenshire]

Hi,

1. SUMPRODUCT is used instead of entering the formula as an array because
they calculate faster, and don't require array entry.
2. You are doing a count in this case, even though the function is
SUM-PRODUCT. The Sheet1!A$2:A$20=A2 portion of the formula returns an array
of the form
{TRUE,FALSE,FALSE,TRUE,....}
When you use -- this converts the TRUE's to 1 and the FALSE's to 0
The second portion of the formula, Sheet1!B$2:B$20="Late" works the same
way. The SUMPRODUCT function multiplies the elements of the two arrays
together and then sums them:
{1,0,0,1,.....} times {1,1,0,0,......} = {1,0,0,0,........}
a 1 times a 1 is 1 all other combinations return 0. When you some these you
are summing those items that met both conditions, you are summing all the
1's. Which give you the count of the number that met both conditions.

 

[newbie_010108]

amazing! thanks!

Hi,

1. SUMPRODUCT is used instead of entering the formula as an array because
they calculate faster, and don't require array entry.
2. You are doing a count in this case, even though the function is
SUM-PRODUCT. The Sheet1!A$2:A$20=A2 portion of the formula returns an array
of the form
{TRUE,FALSE,FALSE,TRUE,....}
When you use -- this converts the TRUE's to 1 and the FALSE's to 0
The second portion of the formula, Sheet1!B$2:B$20="Late" works the same
way. The SUMPRODUCT function multiplies the elements of the two arrays
together and then sums them:
{1,0,0,1,.....} times {1,1,0,0,......} = {1,0,0,0,........}
a 1 times a 1 is 1 all other combinations return 0. When you some these you
are summing those items that met both conditions, you are summing all the
1's. Which give you the count of the number that met both conditions.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire