|
count if
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 |
count if
Hi,
=SUMPRODUCT(--(Sheet1!A$2:A$20=A2),--(Sheet1!B$2:B$20="Late")) Assuming titles on the first row in both sheets. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "newbie_010108" wrote: 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 |
count if
Try this:
Entered on sheet2 B2 then copied down as needed: =SUMPRODUCT(--(Sheet1!A$2:A$10=A2),--(Sheet1!B$2:B$10="late")) -- Biff Microsoft Excel MVP "newbie_010108" wrote in message ... 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 |
count if
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" wrote: Hi, =SUMPRODUCT(--(Sheet1!A$2:A$20=A2),--(Sheet1!B$2:B$20="Late")) Assuming titles on the first row in both sheets. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "newbie_010108" wrote: 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 |
count if
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 "newbie_010108" wrote: 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" wrote: Hi, =SUMPRODUCT(--(Sheet1!A$2:A$20=A2),--(Sheet1!B$2:B$20="Late")) Assuming titles on the first row in both sheets. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "newbie_010108" wrote: 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 |
count if
amazing! thanks!
"Shane Devenshire" wrote: 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 "newbie_010108" wrote: 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" wrote: Hi, =SUMPRODUCT(--(Sheet1!A$2:A$20=A2),--(Sheet1!B$2:B$20="Late")) Assuming titles on the first row in both sheets. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "newbie_010108" wrote: 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 |
| All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com