Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
You previously wrote (quote: "I need to know how many people
ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
I understand what you did, but it wont work for some reason.. I uses those
from another sheet like called TRACKING LIST which i know how and i used the number from another sheet where the function have =E2-(SUM(G2:DC2)) in one cell. What I did, but it wont work. =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1);('TRACKING LIST'!E1:E1000)) It gave me an error... Sorry for bugging you.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
even the single sheet wont work...
"Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
Send me your workbook as an attachment to , or
upload it to http://cjoint.com/ and post back the link. Cheers, -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
Replace the semicolon (;) by a plain comma (,):
=SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
It is working. I figured that you have differnet version for French. Im happy
with it, but there is one more that i dont understand. If you look in TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use Ctrl-F to find that name, but it wont work. Why? Thanks.. "Ardus Petus" wrote: Replace the semicolon (;) by a plain comma (,): =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
You must tick "Values" instead of "Formulas" in "search in" options
HTH -- AP "Jeff" a écrit dans le message de news: ... It is working. I figured that you have differnet version for French. Im happy with it, but there is one more that i dont understand. If you look in TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use Ctrl-F to find that name, but it wont work. Why? Thanks.. "Ardus Petus" wrote: Replace the semicolon (;) by a plain comma (,): =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
One more thing, Sorry.... what is the function that use SB and 1 and B.. I tried =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(D1:D1000 ="B") and it wont work. Thanks. "Ardus Petus" wrote: You must tick "Values" instead of "Formulas" in "search in" options HTH -- AP "Jeff" a écrit dans le message de news: ... It is working. I figured that you have differnet version for French. Im happy with it, but there is one more that i dont understand. If you look in TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use Ctrl-F to find that name, but it wont work. Why? Thanks.. "Ardus Petus" wrote: Replace the semicolon (;) by a plain comma (,): =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
You're counting the number of cells that have a 1 in D1:1000 and at the same
time have a B in D1:D1000. That number is going to be 0. Did you mean to use the same column for both 1 and B? Jeff wrote: One more thing, Sorry.... what is the function that use SB and 1 and B.. I tried =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(D1:D1000 ="B") and it wont work. Thanks. "Ardus Petus" wrote: You must tick "Values" instead of "Formulas" in "search in" options HTH -- AP "Jeff" a écrit dans le message de news: ... It is working. I figured that you have differnet version for French. Im happy with it, but there is one more that i dont understand. If you look in TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use Ctrl-F to find that name, but it wont work. Why? Thanks.. "Ardus Petus" wrote: Replace the semicolon (;) by a plain comma (,): =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
Yes,
In the column of D you will find 1 or 2 or B or STAFF. Everything had to be sort by unit/session like you see the pervious email. I wanted to add up for the people who are in 1 and B. It still have to sort by unit.. Thanks. "Dave Peterson" wrote: You're counting the number of cells that have a 1 in D1:1000 and at the same time have a B in D1:D1000. That number is going to be 0. Did you mean to use the same column for both 1 and B? Jeff wrote: One more thing, Sorry.... what is the function that use SB and 1 and B.. I tried =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(D1:D1000 ="B") and it wont work. Thanks. "Ardus Petus" wrote: You must tick "Values" instead of "Formulas" in "search in" options HTH -- AP "Jeff" a écrit dans le message de news: ... It is working. I figured that you have differnet version for French. Im happy with it, but there is one more that i dont understand. If you look in TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use Ctrl-F to find that name, but it wont work. Why? Thanks.. "Ardus Petus" wrote: Replace the semicolon (;) by a plain comma (,): =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif and and
Yes,
In the column of D you will find 1 or 2 or B or STAFF. Everything had to be sort by unit/session like you see the pervious email. I wanted to add up for the people who are in 1 and B. It still have to sort by unit.. Thanks. "Dave Peterson" wrote: You're counting the number of cells that have a 1 in D1:1000 and at the same time have a B in D1:D1000. That number is going to be 0. Did you mean to use the same column for both 1 and B? Jeff wrote: One more thing, Sorry.... what is the function that use SB and 1 and B.. I tried =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(D1:D1000 ="B") and it wont work. Thanks. "Ardus Petus" wrote: You must tick "Values" instead of "Formulas" in "search in" options HTH -- AP "Jeff" a écrit dans le message de news: ... It is working. I figured that you have differnet version for French. Im happy with it, but there is one more that i dont understand. If you look in TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use Ctrl-F to find that name, but it wont work. Why? Thanks.. "Ardus Petus" wrote: Replace the semicolon (;) by a plain comma (,): =SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING LIST'!D1:D1000=1),('TRACKING LIST'!E1:E1000)) I use a french version of Excel, and often forget to replace my ;'s by ,'s Cheers -- AP "Jeff" a écrit dans le message de news: ... even the single sheet wont work... "Ardus Petus" wrote: You previously wrote (quote: "I need to know how many people ordered water in unit and session.") Now, if you want the number of bottles, enter: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1);(E1:E1000 )) Cheers, -- AP "Jeff" a écrit dans le message de news: ... Please read below. It is works, but I am looking for something else. How I add up the number of the bottles that ordered in SB and session 1. Thanks, Jeff "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)*(E1:E1000 <"")) "Jeff" a écrit dans le message de news: ... This is not what I wanted.. What you did, you count how many people in unit SB in session 1.. I have to add in there to see how many people ordered water.. If nobody ordered water then dont count.... as you see the column E that some people ordered 50 or 75 or 100. I need to know how many people ordered water in unit and session. Thank you. "Ardus Petus" wrote: =SUMPRODUCT((C1:C1000="SB")*(D1:D1000=1)) HTH -- AP "Jeff" a écrit dans le message de news: ... How I use the countif and AND for the below table. I am looking to count a number that involved in Unit/Session/Total which has over 500 names. For example, In SB, how many people ordered water in session one. It is applied to other like in HSB, how many people ordered water in session two. (If the total water ordered is blank means didn't order.) Key Column A is Last name Column B is First name Column C is Unit Column D is Session Column E is Total ordered Last Name First Name Unit Session TOTAL WATER ORDERED LastName1 FirstName1 RVG 1 LastName2 FirstName2 SB 1 LastName3 FirstName3 SG B LastName4 FirstName4 RVG 2 LastName5 FirstName5 SB 1 50 LastName6 FirstName6 SB 1 LastName7 FirstName7 RPG 2 LastName8 FirstName8 HSB 1 75 LastName9 FirstName9 RVG 2 50 LastName10 FirstName10 HSG 2 50 LastName11 FirstName11 RVB 2 50 LastName12 FirstName12 RVB 2 LastName13 FirstName13 HSB 2 LastName14 FirstName14 RVB 1 50 LastName15 FirstName15 RPG 2 LastName16 FirstName16 HSG 1 50 LastName17 FirstName17 HSB 2 100 LastName18 FirstName18 HSB 2 LastName19 FirstName19 SB STAFF 50 LastName20 FirstName20 SRC STAFF 59 LastName21 FirstName21 LS STAFF 58 LastName22 FirstName22 HSB STAFF 50 LastName23 FirstName23 RVG STAFF 75 LastName24 FirstName24 SB STAFF 75 LastName25 FirstName25 TENNIS STAFF 100 Thank you. Jeff -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif ? | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |