Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? |
#2
|
|||
|
|||
Answer: Count If Column A = x and Column B = y
Yes, it is definitely possible to use the COUNTIF function with multiple criteria in different columns. Here's how you can do it:
Note: Make sure that the criteria are enclosed in quotation marks and separated by commas. Also, make sure that the ranges are enclosed in parentheses and separated by commas.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))
"Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
"Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Trish, please try:
=COUNTIFS(A1:A100,"A",B1:B100,"B") Please advise if that is what you were looking for. Trish wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
There's no reason Sumproduct won't work in this situation.
You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
This works only in Excel 2007. You should identify this when you are using
functions found only in the newest version. Regards, Fred. "Chin via OfficeKB.com" <u50624@uwe wrote in message news:94e93ddbaa04b@uwe... Trish, please try: =COUNTIFS(A1:A100,"A",B1:B100,"B") Please advise if that is what you were looking for. Trish wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
I think I found the problem: Can Sumproduct compare cell content to another
cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
You are missing the arithmetic operator, and have extraneous commas. Try the
following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Fred I SO appreciate the effort.
I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
1. To determine whether to use 'Client List' or 'Client_List', look at the
name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
I get "#NAME?" error ;(
"Chin via OfficeKB.com" wrote: Trish, please try: =COUNTIFS(A1:A100,"A",B1:B100,"B") Please advise if that is what you were looking for. Trish wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Ahhh, ok, I have 2003
"Fred Smith" wrote: This works only in Excel 2007. You should identify this when you are using functions found only in the newest version. Regards, Fred. "Chin via OfficeKB.com" <u50624@uwe wrote in message news:94e93ddbaa04b@uwe... Trish, please try: =COUNTIFS(A1:A100,"A",B1:B100,"B") Please advise if that is what you were looking for. Trish wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Awesome, this worked great -- can you use if for more than two conditions?
"Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Yes, countifs( ) works only in Excel 2007. Not sure about sumproduct( ).
Please try: =SUMPRODUCT((A1:A100="A")*(B1:B100="B")) Please advise if this works in Excel 2003. Trish wrote: I get "#NAME?" error ;( Trish, please try: [quoted text clipped - 6 lines] the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Fred, I've been playing with this on an off for months and I think I see my
confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
We've sure been beating around the bush if all you want to do is sum a
column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred "Altair1972m" wrote in message ... Fred, I've been playing with this on an off for months and I think I see my confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Can you tell me how to email you the file so you can see it? Then you'll see
what I did and how to make it easier using the sumproduct for when I do this sort of thing in the future? "Fred Smith" wrote: We've sure been beating around the bush if all you want to do is sum a column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Sorry Fred, I didn't answer your question. No I am not trying to sum a column.
I am trying to find out what clients came in, to a VOLAG point of service, who were refugees, during a given month. Each of those criteria is measured in a separate column. I am also in another sheet in the document trying to see which of those clients (all the criteria above) did not have their 90 day health services (examinations etc) process completed within 90 days of entry. I solved the problem with concatination, but was curious about the sumproduct function because it would both eliminate the need for all the extra concatination columns and at the same time, automate the process so someone who takes over after me will not have to fill down the columns. "Fred Smith" wrote: We've sure been beating around the bush if all you want to do is sum a column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred "Altair1972m" wrote in message ... Fred, I've been playing with this on an off for months and I think I see my confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Fred I found the problem. It does not seem to work between worksheets.
In sheet one I made 3 simple columns: B 2 X B 2 X A 1 Y B 1 Y B 1 X B 2 X B 2 X I ran the basic subproduct =+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X")) and got - 1 Then I inserted a new sheet I put in =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Answer - 0 =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X")) Answer - #VALUE I stopped there and copied this for you. So...does sumproduct work across pages? If not, do not feel like this was a complete waste of time, because I can still eliminate most of my concatinations. "Fred Smith" wrote: We've sure been beating around the bush if all you want to do is sum a column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred "Altair1972m" wrote in message ... Fred, I've been playing with this on an off for months and I think I see my confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
All you need to do is specify your ranges properly.
Instead of: =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Use: =SUMPRODUCT(Sheet1!$A$2:$A$20="B") Note in this formula, you specified the range properly the first time, but not the second time: =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) Use this instead: =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:B $20=1)) The best way to insert ranges into a formula is to get Excel to do it. Contrary to humans, Excel will specify the range properly every time. When writing a formula, do the following (using your Sumproduct as an example): Enter: =sumproduct(( Now highlight the range you want to use. Go to Sheet2, and highlight cells a2:a20. Watch the navigation bar, and you will see Excel insert the proper range addresses. If you want an absolute address, press F4. Continue entering the formula you want, as in: ="B")*( Highlight your second range Finish off the formula: )) Regards, Fred "Altair1972m" wrote in message ... Fred I found the problem. It does not seem to work between worksheets. In sheet one I made 3 simple columns: B 2 X B 2 X A 1 Y B 1 Y B 1 X B 2 X B 2 X I ran the basic subproduct =+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X")) and got - 1 Then I inserted a new sheet I put in =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Answer - 0 =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X")) Answer - #VALUE I stopped there and copied this for you. So...does sumproduct work across pages? If not, do not feel like this was a complete waste of time, because I can still eliminate most of my concatinations. "Fred Smith" wrote: We've sure been beating around the bush if all you want to do is sum a column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred "Altair1972m" wrote in message ... Fred, I've been playing with this on an off for months and I think I see my confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. What I did was create some columns off to the right of the main spreadsheet. These columns were IF then statements such as =IF(I3="r",+B3&C3,"") where I is status B3 is Volag C3 is County That one being in S In the U column I put the year and month concatenated in =+S3&+YEAR(A3)&+MONTH(A3) Where A was the date of arrival I suppose that could have been one command, but I had other sheets working off the data of where. In the Total Sheet I had Column A Volag Column B County Row 2 Date by month Finally the calculation for the total was =+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)). And that totaled the number of people who came in on that date in that county with that assigned VOLAG. I'd post the sheet, but I don't think you do an attachment. |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
Well...one correction, when I let the computer pick, it did not include the *
so I had to go back and fix it, but for the first time, I saw it work inter sheet. Maybe now I can do this with the other file. Talk to you in another 3 months. :) "Fred Smith" wrote: All you need to do is specify your ranges properly. Instead of: =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Use: =SUMPRODUCT(Sheet1!$A$2:$A$20="B") Note in this formula, you specified the range properly the first time, but not the second time: =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) Use this instead: =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:B $20=1)) The best way to insert ranges into a formula is to get Excel to do it. Contrary to humans, Excel will specify the range properly every time. When writing a formula, do the following (using your Sumproduct as an example): Enter: =sumproduct(( Now highlight the range you want to use. Go to Sheet2, and highlight cells a2:a20. Watch the navigation bar, and you will see Excel insert the proper range addresses. If you want an absolute address, press F4. Continue entering the formula you want, as in: ="B")*( Highlight your second range Finish off the formula: )) Regards, Fred "Altair1972m" wrote in message ... Fred I found the problem. It does not seem to work between worksheets. In sheet one I made 3 simple columns: B 2 X B 2 X A 1 Y B 1 Y B 1 X B 2 X B 2 X I ran the basic subproduct =+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X")) and got - 1 Then I inserted a new sheet I put in =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Answer - 0 =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X")) Answer - #VALUE I stopped there and copied this for you. So...does sumproduct work across pages? If not, do not feel like this was a complete waste of time, because I can still eliminate most of my concatinations. "Fred Smith" wrote: We've sure been beating around the bush if all you want to do is sum a column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred "Altair1972m" wrote in message ... Fred, I've been playing with this on an off for months and I think I see my confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
That's right, Excel will not enter operators. You have to do that yourself.
You will see that my instructions stated you needed to enter the * (and the parentheses) yourself. Regards, Fred "Altair1972m" wrote in message ... Well...one correction, when I let the computer pick, it did not include the * so I had to go back and fix it, but for the first time, I saw it work inter sheet. Maybe now I can do this with the other file. Talk to you in another 3 months. :) "Fred Smith" wrote: All you need to do is specify your ranges properly. Instead of: =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Use: =SUMPRODUCT(Sheet1!$A$2:$A$20="B") Note in this formula, you specified the range properly the first time, but not the second time: =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) Use this instead: =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:B $20=1)) The best way to insert ranges into a formula is to get Excel to do it. Contrary to humans, Excel will specify the range properly every time. When writing a formula, do the following (using your Sumproduct as an example): Enter: =sumproduct(( Now highlight the range you want to use. Go to Sheet2, and highlight cells a2:a20. Watch the navigation bar, and you will see Excel insert the proper range addresses. If you want an absolute address, press F4. Continue entering the formula you want, as in: ="B")*( Highlight your second range Finish off the formula: )) Regards, Fred "Altair1972m" wrote in message ... Fred I found the problem. It does not seem to work between worksheets. In sheet one I made 3 simple columns: B 2 X B 2 X A 1 Y B 1 Y B 1 X B 2 X B 2 X I ran the basic subproduct =+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X")) and got - 1 Then I inserted a new sheet I put in =SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B") Answer - 0 =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1)) =SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X")) Answer - #VALUE I stopped there and copied this for you. So...does sumproduct work across pages? If not, do not feel like this was a complete waste of time, because I can still eliminate most of my concatinations. "Fred Smith" wrote: We've sure been beating around the bush if all you want to do is sum a column. Solutions to this problem are easily found. What column do you want to sum? What are the conditions that need to be met? What version of Excel do you have? Regards, Fred "Altair1972m" wrote in message ... Fred, I've been playing with this on an off for months and I think I see my confusion: I was trying to sum the contents of a column. Was this command you were instructing designed to sum the column or PROVIDE ME with the judgement call on whether this column should be part of the summation? "Fred Smith" wrote: 1. To determine whether to use 'Client List' or 'Client_List', look at the name of the spreadsheet on the tabs at the bottom. The name you use in your formulas must match the name on the tab of the spreadsheet. 2. I had trouble with the copy and paste as well. When I created the formula, I simply modified the one you has posted. However, when I pasted that into Excel, it complained about the quotes. They weren't regular quotes, but smart quotes, which Excel doesn't like. When I changed the quotes, Excel accepted the formula. Here's a copy of what I used: =SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client List'!I$2:I$60000="R")) Remember, if this appears on more than one line in your message, you will need to delete the word wrap. Regards, Fred. "Altair1972m" wrote in message ... Fred I SO appreciate the effort. I'm just not used to being this success impaired. I cut and pasted your formula in and then added the + at the beginning. It still says there is a problem with the first array. And I even tried changing 'Client List' to 'Client_List' but that didn't work. "Fred Smith" wrote: You are missing the arithmetic operator, and have extraneous commas. Try the following: Sumproduct(('Client List'!A$2:A$60000=E$2)*(Client List!B$2:B$60000=$A4)*(Client List!C$2:C$60000=$B4)*(Client List!I$2:I$60000=R)) Regards, Fred "Altair1972m" wrote in message ... I think I found the problem: Can Sumproduct compare cell content to another cell content? I am so game for this, but I have been trying to play with the command on and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee dee deeeeee here. Im trying Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) Client List Column A is the dates. Client List Column B is the volags. Client List C is the County, Client List I is the Status as refugee. On the Totals sheet the dates run across Row 2. Totals of the year are the next row using the sum command, and then the next 12 rows list the monthy totals with the criteria in A, B, and C The formula above is In Cell E4 which is under the 2008-01 date, the USCC VOLAG in Atlantic County Here is the total table 2008-01 0 USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(Client List!B$2:B$60000,=$A4)(Client List!C$2:C$60000,=$B4)( Client List!I$2:I$60000,=R) USCC CAMDEN 0 USCC ESSEX 0 USCC PASSAIC 0 USCC MERCER 0 LIRS ESSEX 0 LIRS MERCER 0 JFS BERGEN 0 JVS ESSEX 0 JFVS MIDDLESEX 0 IRC UNION 0 IRSA HUDSON 0 I put it in and got an error. I assumed I had a problem with the date, so I tried the formula again this time without the reference to clientlist A Still have an error. I think I found the problem: Can Sumproduct compare to a cell content? So then I tried this: +Sumproduct((Client List!B$2:B$60000,="USCC")(Client List!C$2:C$60000,="ATLANTIC")( Client List!I$2:I$60000,=R)) Just to see if that was the problem. I still had an error. Help please! "Sometimes the genius and the idiot are the same person" --Nitchie "Fred Smith" wrote: There's no reason Sumproduct won't work in this situation. You should spend some time getting to know it, as it's a very useful function. It's worth every minute you spend on it. By the way, the pluses in your formulae are superfluous. =S3&YEAR(A3)&MONTH(A3) works just as well, and will be easier for others to understand. Regards, Fred. "Altair1972m" wrote in message ... "Teethless mama" wrote: =SUMPRODUCT((A1:A100="x")*(B1:B100="y")) "Trish" wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? I had this same question earlier in the year. I just tried to plug in the sumproduct command and it didn't work for me. I'm going to give you a much longer method of doing it which was my work around. I had 4 categories to marry: Status of Client Date of Arrival VOLAG County And what I was doing was counting all clients arriving during a month from a volag in certain counties on a separate page. |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Column A = x and Column B = y
On Tuesday, April 21, 2009 at 10:39:46 AM UTC+10, Chin via OfficeKB.com wrote:
Trish, please try: =COUNTIFS(A1:A100,"A",B1:B100,"B") Please advise if that is what you were looking for. Trish wrote: I am trying to use the count function where two conditions in different columns need to be satisfied and having no luck. Basically I want to look in the first column to see if it's A, then look in 2nd column to see if it's B, then count it... is that possible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200904/1 Hi Chin - this works great for two fields, but not for three do you know a workaround for this? Ie I want to count how many letters (Column A), sent to person (Column B), responded to Y or N (Columnn C) Thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Based on Mulipple Criteria acrossed Column count last column | Excel Worksheet Functions | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Count entries in one column based on values in another column | Excel Worksheet Functions | |||
SUM/COUNT column(s) based on specific value present within the column | Excel Worksheet Functions |