Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct question
Hello!
I need to modify a formula to sum 5 counties together and return what is in column 4. Column 2 is blank so I do not need to match column 1 city with the county in column 2. I just need to find the "County: " in column 1 find the amount in column 4 and add all counties column 4 together. Currently I have in worksheet 1 a formual: =sumproduct(--(index(ilsort,,1)="Totals for City: Chicago"),--Index(ilsort,,2)="County: Cook")+(index(ilsort,,2)="County: Dupage"), index(ilsort,,4)) looks in range name ILsort for column 1 and find Total of City Chicago then look in column 2 for "Chicago city in Cook and Dupage counties add column 4 of each county and returns total Worksheet 2 has the data: Colmn 1 = City Column 2 = County Column 3 = State Column 4 = Gross Column 5 = Tax How do I modify the formula to say: Look in range ILsort column 1 for "County Cook", County Dupage, County Kings, find the amount in column 4 of each county and return the total I've tried to change the formual but zero keeps coming up. Its not working. I also have another formula I've tried to modify: =IF(ISNA(VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)),0,VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)) but I cant seem to get this one to work either. I'm not familiar enough with the formula's to figure out the order they should be in. The help examples in excel and not that helpful. Any help would be wonderful. Thanks |
#2
|
|||
|
|||
Is this what you mean
=SUMPRODUCT(--(INDEX(iLSort,,1)="Totals for City: Chicago"),--((INDEX(iLSort,,2)="County: Cook")+(INDEX(iLSort,,2)="County: Durango")+(INDEX(iLSort,,2)="County: Kings")), INDEX(iLSort,,4)) -- HTH RP (remove nothere from the email address if mailing direct) "taxmom" wrote in message ... Hello! I need to modify a formula to sum 5 counties together and return what is in column 4. Column 2 is blank so I do not need to match column 1 city with the county in column 2. I just need to find the "County: " in column 1 find the amount in column 4 and add all counties column 4 together. Currently I have in worksheet 1 a formual: =sumproduct(--(index(ilsort,,1)="Totals for City: Chicago"),--Index(ilsort,,2)="County: Cook")+(index(ilsort,,2)="County: Dupage"), index(ilsort,,4)) looks in range name ILsort for column 1 and find Total of City Chicago then look in column 2 for "Chicago city in Cook and Dupage counties add column 4 of each county and returns total Worksheet 2 has the data: Colmn 1 = City Column 2 = County Column 3 = State Column 4 = Gross Column 5 = Tax How do I modify the formula to say: Look in range ILsort column 1 for "County Cook", County Dupage, County Kings, find the amount in column 4 of each county and return the total I've tried to change the formual but zero keeps coming up. Its not working. I also have another formula I've tried to modify: =IF(ISNA(VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)),0,VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)) but I cant seem to get this one to work either. I'm not familiar enough with the formula's to figure out the order they should be in. The help examples in excel and not that helpful. Any help would be wonderful. Thanks |
#3
|
|||
|
|||
THANKS,
I didn't need the city name because I am only adding the counties together. I tried and tried, and couldn't get it to work. Then I used yours response and just removed the City name, it worked. thanks, so much just the help I needed. "Bob Phillips" wrote: Is this what you mean =SUMPRODUCT(--(INDEX(iLSort,,1)="Totals for City: Chicago"),--((INDEX(iLSort,,2)="County: Cook")+(INDEX(iLSort,,2)="County: Durango")+(INDEX(iLSort,,2)="County: Kings")), INDEX(iLSort,,4)) -- HTH RP (remove nothere from the email address if mailing direct) "taxmom" wrote in message ... Hello! I need to modify a formula to sum 5 counties together and return what is in column 4. Column 2 is blank so I do not need to match column 1 city with the county in column 2. I just need to find the "County: " in column 1 find the amount in column 4 and add all counties column 4 together. Currently I have in worksheet 1 a formual: =sumproduct(--(index(ilsort,,1)="Totals for City: Chicago"),--Index(ilsort,,2)="County: Cook")+(index(ilsort,,2)="County: Dupage"), index(ilsort,,4)) looks in range name ILsort for column 1 and find Total of City Chicago then look in column 2 for "Chicago city in Cook and Dupage counties add column 4 of each county and returns total Worksheet 2 has the data: Colmn 1 = City Column 2 = County Column 3 = State Column 4 = Gross Column 5 = Tax How do I modify the formula to say: Look in range ILsort column 1 for "County Cook", County Dupage, County Kings, find the amount in column 4 of each county and return the total I've tried to change the formual but zero keeps coming up. Its not working. I also have another formula I've tried to modify: =IF(ISNA(VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)),0,VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)) but I cant seem to get this one to work either. I'm not familiar enough with the formula's to figure out the order they should be in. The help examples in excel and not that helpful. Any help would be wonderful. Thanks |
#4
|
|||
|
|||
I thought that mine wasn't exactly right, but I hoped to get close enough
that you could apply the final touch. The dream team as they say :-) Bob "taxmom" wrote in message ... THANKS, I didn't need the city name because I am only adding the counties together. I tried and tried, and couldn't get it to work. Then I used yours response and just removed the City name, it worked. thanks, so much just the help I needed. "Bob Phillips" wrote: Is this what you mean =SUMPRODUCT(--(INDEX(iLSort,,1)="Totals for City: Chicago"),--((INDEX(iLSort,,2)="County: Cook")+(INDEX(iLSort,,2)="County: Durango")+(INDEX(iLSort,,2)="County: Kings")), INDEX(iLSort,,4)) -- HTH RP (remove nothere from the email address if mailing direct) "taxmom" wrote in message ... Hello! I need to modify a formula to sum 5 counties together and return what is in column 4. Column 2 is blank so I do not need to match column 1 city with the county in column 2. I just need to find the "County: " in column 1 find the amount in column 4 and add all counties column 4 together. Currently I have in worksheet 1 a formual: =sumproduct(--(index(ilsort,,1)="Totals for City: Chicago"),--Index(ilsort,,2)="County: Cook")+(index(ilsort,,2)="County: Dupage"), index(ilsort,,4)) looks in range name ILsort for column 1 and find Total of City Chicago then look in column 2 for "Chicago city in Cook and Dupage counties add column 4 of each county and returns total Worksheet 2 has the data: Colmn 1 = City Column 2 = County Column 3 = State Column 4 = Gross Column 5 = Tax How do I modify the formula to say: Look in range ILsort column 1 for "County Cook", County Dupage, County Kings, find the amount in column 4 of each county and return the total I've tried to change the formual but zero keeps coming up. Its not working. I also have another formula I've tried to modify: =IF(ISNA(VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)),0,VLOOKUP("Totals for County: WESTCHESTER",nysort,4,FALSE)) but I cant seem to get this one to work either. I'm not familiar enough with the formula's to figure out the order they should be in. The help examples in excel and not that helpful. Any help would be wonderful. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |