|
Combining IF, ANd and SUM functions in a formula
I'm trying to add the amounts in a column where two other columns match the
set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) |
Combining IF, ANd and SUM functions in a formula
"RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Here we go: in what cell is your SUM totaled, say A:10000 and C10000.. Then: IF (AND (SOURCE!$A$10000="X",Source!$C$10000="Y"),SUM(Sour ce!$G$1:$G$9999),0) FLKulchar |
Combining IF, ANd and SUM functions in a formula
"RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) or, you need a SUM formula as follows:=IF(AND(SUM(Source!$A$2:$A$9999)="X",SUM(S ource!$C$2:$C$9999)="Y"),SUM(Source!$G$1:$G$9999), 0) FLKulchar |
Combining IF, ANd and SUM functions in a formula
try
=sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1 :$C$9999="Y")*Source!$G$1:$G$9999) -- Don Guillett Microsoft MVP Excel SalesAid Software "RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) |
Combining IF, ANd and SUM functions in a formula
On Jan 4, 9:42 am, RJanz wrote:
I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Maybe =SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C $9999="Y"),Source!$G2:$G9999) which sums only those column G values that are in rows where column A has X or x and column C has Y or y. Ken Johnson |
Combining IF, ANd and SUM functions in a formula
On Jan 4, 10:25 am, Ken Johnson wrote:
On Jan 4, 9:42 am, RJanz wrote: I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Maybe =SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C $9999="Y"),Source!$G2:$G9999) which sums only those column G values that are in rows where column A has X or x and column C has Y or y. Ken Johnson Also, be careful with the range of row values, they must correspond ie either 1 to 9999 for each of A, C and G, or 2 to 9999 for each of A, C and G, not a mixture, otherwise you will get the #VALUE! result. Ken Johnson |
Combining IF, ANd and SUM functions in a formula
The formula is in a separate worksheet where I am trying to combine various
people (Y) with projects (x). Y is a person's name and x is a project number so they can't be summed. I am trying to sum the number of hours recorded for each project. thanks "FLKulchar" wrote: "RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Here we go: in what cell is your SUM totaled, say A:10000 and C10000.. Then: IF (AND (SOURCE!$A$10000="X",Source!$C$10000="Y"),SUM(Sour ce!$G$1:$G$9999),0) FLKulchar |
Combining IF, ANd and SUM functions in a formula
This was helpful but returns a #Value response. Is that because x and y are
text and not amounts? thanks "Ken Johnson" wrote: On Jan 4, 9:42 am, RJanz wrote: I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Maybe =SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C $9999="Y"),Source!$G2:$G9999) which sums only those column G values that are in rows where column A has X or x and column C has Y or y. Ken Johnson |
Combining IF, ANd and SUM functions in a formula
this also returns a #Value response, possibly because x and y are text values.
thanks "Don Guillett" wrote: try =sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1 :$C$9999="Y")*Source!$G$1:$G$9999) -- Don Guillett Microsoft MVP Excel SalesAid Software "RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) |
Combining IF, ANd and SUM functions in a formula
The row ranges correspond.
thanks "Ken Johnson" wrote: On Jan 4, 10:25 am, Ken Johnson wrote: On Jan 4, 9:42 am, RJanz wrote: I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Maybe =SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C $9999="Y"),Source!$G2:$G9999) which sums only those column G values that are in rows where column A has X or x and column C has Y or y. Ken Johnson Also, be careful with the range of row values, they must correspond ie either 1 to 9999 for each of A, C and G, or 2 to 9999 for each of A, C and G, not a mixture, otherwise you will get the #VALUE! result. Ken Johnson |
Combining IF, ANd and SUM functions in a formula
Column A has about 9 different text variables and column C has about 50
variables. When I evaluate the formula, it seems to try to convert each false and true to a number rather than just adding the number in column G. thanks "Ken Johnson" wrote: On Jan 4, 9:42 am, RJanz wrote: I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$999 9="Y"),SUM(Source!$G$1:$G$9999),0) Maybe =SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C $9999="Y"),Source!$G2:$G9999) which sums only those column G values that are in rows where column A has X or x and column C has Y or y. Ken Johnson |
Combining IF, ANd and SUM functions in a formula
=sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1 :$C$9999="Y")*Source!$G$1:$G$9999)
not tested but try? =sumproduct(--(Source!$A$2:$A$9999="X"),--(Source!$C$1:$C$9999="Y"),Source!$G$1:$G$9999)--Don GuillettMicrosoft MVP ExcelSalesAid "RJanz" wrote in ... this also returns a #Value response, possibly because x and y are textvalues. thanks "Don Guillett" wrote: try=sumproduct((Source!$A$2:$A$9999="X")*(Source !$C$1:$C$9999="Y")*Source!$G$1:$G$9999) -- Don Guillett Microsoft MVP Excel SalesAid Software "RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$99 99="Y"),SUM(Source!$G$1:$G$9999),0) |
Combining IF, ANd and SUM functions in a formula
If desired, send your file to me at the address below.
-- Don Guillett Microsoft MVP Excel SalesAid Software "RJanz" wrote in message ... This overcomes the #Value response but returns a value of 0 when it should actually have a value. thanks "Don Guillett" wrote: =sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1 :$C$9999="Y")*Source!$G$1:$G$9999) not tested but try? =sumproduct(--(Source!$A$2:$A$9999="X"),--(Source!$C$1:$C$9999="Y"),Source!$G$1:$G$9999)--Don GuillettMicrosoft MVP ExcelSalesAid "RJanz" wrote in ... this also returns a #Value response, possibly because x and y are textvalues. thanks "Don Guillett" wrote: try=sumproduct((Source!$A$2:$A$9999="X")*(Source !$C$1:$C$9999="Y")*Source!$G$1:$G$9999) -- Don Guillett Microsoft MVP Excel SalesAid Software "RJanz" wrote in message ... I'm trying to add the amounts in a column where two other columns match the set criteria, however, it is comparing the first cell only to the text specified. Does anyone know how I overcome this or correct the formula below? =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9 999="Y"),SUM(Source!$G$1:$G$9999),0) |
| All times are GMT +1. The time now is 11:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com