 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / January 2008Combining 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$9999="Y"),SUM(Source!$G$1:$G$9999),0) > I'm trying to add the amounts in a column where two other columns match > the [quoted text clipped - 3 lines] > > =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="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(Source!$G$1:$G$9999),0) FLKulchar 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 > > I'm trying to add the amounts in a column where two other columns match > > the [quoted text clipped - 12 lines] > > > I'm trying to add the amounts in a column where two other columns match > the [quoted text clipped - 3 lines] > > =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="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(Source!$C$2:$C$9999)="Y"),SUM(Source!$G$1:$G$9999),0) FLKulchar try =sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999)  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > I'm trying to add the amounts in a column where two other columns match > the [quoted text clipped - 3 lines] > > =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0) this also returns a #Value response, possibly because x and y are text values. thanks > try > =sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999) [quoted text clipped - 6 lines] > > > > =IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0) > =sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999) not tested but try? This overcomes the #Value response but returns a value of 0 when it should actually have a value. thanks > > =sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999) > not tested but try? >=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)>>>> If desired, send your file to me at the address below. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > This overcomes the #Value response but returns a value of 0 when it should > actually have a value. [quoted text clipped - 17 lines] >> formula>> > below?>> >>> >>=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)>>>> > 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$9999="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 > > 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 [quoted text clipped - 9 lines] > 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 The row ranges correspond. thanks > > > 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 [quoted text clipped - 15 lines] > > Ken Johnson This was helpful but returns a #Value response. Is that because x and y are text and not amounts? thanks > > 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 [quoted text clipped - 9 lines] > has X or x and column C has Y or y. > Ken Johnson > This was helpful but returns a #Value response. Is that because x and y are > text and not amounts? [quoted text clipped - 13 lines] > > has X or x and column C has Y or y. > > Ken Johnson You said earlier that y is a person's name and x is project number. So, say y = "John Smith" and project number is 45 (ie a number, not text) then... =IF(AND(Source!$A$2:$A$9999=45,Source!$C$1:$C$9999="John Smith"),SUM(Source!$G$1:$G$9999),0) should sum the hours spent by John Smith on project 45 from column G. Ken Johnson > > This was helpful but returns a #Value response. Is that because x and y are > > text and not amounts? [quoted text clipped - 24 lines] > > Ken Johnson Oops! I copied the wrong formula. Let me try again... You said earlier that y is a person's name and x is project number. So, say y = "John Smith" and project number is 45 (ie a number, not text) then... =SUMPRODUCT(--($A$2:$A$9999=45),--($C$2:$C$9999="John Smith"),$G$2:$G $9999) should sum the hours spent by John Smith on project 45 from column G. NB name, y, in inverted commas, project number, x, not in inverted commas. Ken Johnson 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 > > 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 [quoted text clipped - 9 lines] > has X or x and column C has Y or y. > Ken Johnson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.