If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
is it possible to sum the product of 2 or more cells, 1 cell being the result
of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it returns '#VALUE!': =HLOOKUP("F",C4:L7,SUMIF(C4:L4,TRUE)) Any suggestions? |
#2
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
Hi,
Maybe this =SUMPRODUCT((C3:L3="Failed")*(C4:L4)) Mike "SimonSNA400" wrote: is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it returns '#VALUE!': =HLOOKUP("F",C4:L7,SUMIF(C4:L4,TRUE)) Any suggestions? |
#3
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
Try something like this...
B2:J2 = F (failed) or C (completed) B3:J3 = quantities =SUMIF(B2:J2,"F",B3:J3) -- Biff Microsoft Excel MVP "SimonSNA400" wrote in message ... is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it returns '#VALUE!': =HLOOKUP("F",C4:L7,SUMIF(C4:L4,TRUE)) Any suggestions? |
#4
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
I'm working with something similar to this post, not sure how to start a new
one, "New Thread" doesn't seem to be an active link for me. Anyhow, I'm using columns that are consistent, have a header row at top and total row at bottom, which updates itself automatically, based on any column filters in use. P2:P100 = textA N2:N100 = quantities M2:M100 = textB L2:L100 = textC I'm currently using =SUMIF(P2:P100,"Refuelling with JetA",N2:N100) for my first set of fields, with great success, updating formula with the correct text for the other sections I'm wanting totalled, independently. Now I've run into a case where I need a secondary condition checked, if the first is met. =SUMIF(P2:P100,"Group payment",N2:N100) However, this can bet met with both incoming and outgoing payments, and currently, that field cannot differentiate the two. TextB and/or TextC determines if it's incoming or outgoing. What I don't know, is how to add the second check. I'll be using one of each. I'm thinking I'll need to add an AND or OR to make it work, but nesting these contexts is something I'm unsure of. SUMIF (A) should total only things that meet "Group payment" in column P, and should sum all amounts in column N only if column L = "SoCal Airways" Likewise, SUMIF (B) should total only things that meet "Group payment" in column P, and should sum all amounts in column N only if column M = "SoCal Airways" One last thing, I know P2:P100 is including only rows 2 through 100, how can I make that all-inclusive, as the sheet loads from an .xml output, and before too long, it will have more than 100 rows. (Another related sheet is about to break 10,000 rows) Thanks for all the people that help out. I've learned loads just reading through others' questions and responses. It's appreciated. "T. Valko" wrote: Try something like this... B2:J2 = F (failed) or C (completed) B3:J3 = quantities =SUMIF(B2:J2,"F",B3:J3) -- Biff Microsoft Excel MVP "SimonSNA400" wrote in message ... is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it returns '#VALUE!': =HLOOKUP("F",C4:L7,SUMIF(C4:L4,TRUE)) Any suggestions? . |
#5
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
Try something like this for multiple conditions.
I like to use cells to hold the criteria. A1 = Group payment B1 = SoCal Airways =SUMPRODUCT(--(P2:P100=A1),--(L1:L100=B1),N1:N100) As far as the size of the data range increasing over time, just increase the size of the referenced ranges: =SUMPRODUCT(--(P2:P1000=A1),--(L1:L1000=B1),N1:N1000) Or, use dynamic ranges: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "Crashin" wrote in message ... I'm working with something similar to this post, not sure how to start a new one, "New Thread" doesn't seem to be an active link for me. Anyhow, I'm using columns that are consistent, have a header row at top and total row at bottom, which updates itself automatically, based on any column filters in use. P2:P100 = textA N2:N100 = quantities M2:M100 = textB L2:L100 = textC I'm currently using =SUMIF(P2:P100,"Refuelling with JetA",N2:N100) for my first set of fields, with great success, updating formula with the correct text for the other sections I'm wanting totalled, independently. Now I've run into a case where I need a secondary condition checked, if the first is met. =SUMIF(P2:P100,"Group payment",N2:N100) However, this can bet met with both incoming and outgoing payments, and currently, that field cannot differentiate the two. TextB and/or TextC determines if it's incoming or outgoing. What I don't know, is how to add the second check. I'll be using one of each. I'm thinking I'll need to add an AND or OR to make it work, but nesting these contexts is something I'm unsure of. SUMIF (A) should total only things that meet "Group payment" in column P, and should sum all amounts in column N only if column L = "SoCal Airways" Likewise, SUMIF (B) should total only things that meet "Group payment" in column P, and should sum all amounts in column N only if column M = "SoCal Airways" One last thing, I know P2:P100 is including only rows 2 through 100, how can I make that all-inclusive, as the sheet loads from an .xml output, and before too long, it will have more than 100 rows. (Another related sheet is about to break 10,000 rows) Thanks for all the people that help out. I've learned loads just reading through others' questions and responses. It's appreciated. "T. Valko" wrote: Try something like this... B2:J2 = F (failed) or C (completed) B3:J3 = quantities =SUMIF(B2:J2,"F",B3:J3) -- Biff Microsoft Excel MVP "SimonSNA400" wrote in message ... is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it returns '#VALUE!': =HLOOKUP("F",C4:L7,SUMIF(C4:L4,TRUE)) Any suggestions? . |
#6
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
That appears to be working correctly:
=SUMPRODUCT(--(P2:P99999="Refuelling with JetA"),--(S2:S99999="N208SA"),N2:N99999) Is there a way to make this all work with =SUMIF instead of =SUMPRODUCT ? Using SUMPRODUCT, if the range includes any empty cells, the result is always #VALUE I tried another route, using: =SUM(IF(($P2:$P24="Refuelling with JetA")*($S2:$S24="N208SA"),N2:N24,"")) but with that I was getting #N/A or #VALUE when the range included any empty cells, forcing me to update the formula each time the number of rows increases (far too often to be reasonable going through and changing the formula). The P99999 seems to be working properly with SUMPRODUCT at this point, but I'd had similar issues in the past (may have been my own error). Thanks for the idea of having static cells with the conditional criteria. I've made a new sheet simply for that purpose, wasn't too tough to change formulae to reference the sheet/cell needed. As it stands now: =SUMPRODUCT(--(P2:P99999=strings!A21),--(S2:S99999=strings!A44),N2:N99999) is providing accurate results, and is the base formula for which changes are being derived from. Looks to be a point where I can get a lot more done for now, thanks a ton! [original left-overs after edit] Sorry to change variables in the middle there... There's basically one formula that will work for all of them, just taking out the parts I don't need. What I'm looking for with the range question, is how to build the formula correctly, and not have to go back in and edit it each time the number of rows increases (very very frequent) - That may work with a dynamic range, but that's secondary to getting the formula to pull the right information, nothing more, nothing less. Would a dynamic range, when created on a table with a different number of rows (identical columns though) be copy/paste suitable for the other tables? Or would each need edited? "T. Valko" wrote: Try something like this for multiple conditions. I like to use cells to hold the criteria. A1 = Group payment B1 = SoCal Airways =SUMPRODUCT(--(P2:P100=A1),--(L1:L100=B1),N1:N100) As far as the size of the data range increasing over time, just increase the size of the referenced ranges: =SUMPRODUCT(--(P2:P1000=A1),--(L1:L1000=B1),N1:N1000) Or, use dynamic ranges: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP |
#7
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
That appears to be working correctly:
=SUMPRODUCT(--(P2:P99999="Refuelling with JetA"),--(S2:S99999="N208SA"),N2:N99999) Is there a way to make this all work with =SUMIF instead of =SUMPRODUCT ? If you're referencing 99,999 rows then you must be using Excel 2007. You can use the SUMIFS function which is much more efficient than SUMPRODUCT. =SUMIFS(N:N,P:P,strings!A21,S:S,strings!A44) This also solves the increasing range problem. You can reference the entire column and SUMIFS will only calculate the actual used range. -- Biff Microsoft Excel MVP "Crashin" wrote in message ... That appears to be working correctly: =SUMPRODUCT(--(P2:P99999="Refuelling with JetA"),--(S2:S99999="N208SA"),N2:N99999) Is there a way to make this all work with =SUMIF instead of =SUMPRODUCT ? Using SUMPRODUCT, if the range includes any empty cells, the result is always #VALUE I tried another route, using: =SUM(IF(($P2:$P24="Refuelling with JetA")*($S2:$S24="N208SA"),N2:N24,"")) but with that I was getting #N/A or #VALUE when the range included any empty cells, forcing me to update the formula each time the number of rows increases (far too often to be reasonable going through and changing the formula). The P99999 seems to be working properly with SUMPRODUCT at this point, but I'd had similar issues in the past (may have been my own error). Thanks for the idea of having static cells with the conditional criteria. I've made a new sheet simply for that purpose, wasn't too tough to change formulae to reference the sheet/cell needed. As it stands now: =SUMPRODUCT(--(P2:P99999=strings!A21),--(S2:S99999=strings!A44),N2:N99999) is providing accurate results, and is the base formula for which changes are being derived from. Looks to be a point where I can get a lot more done for now, thanks a ton! [original left-overs after edit] Sorry to change variables in the middle there... There's basically one formula that will work for all of them, just taking out the parts I don't need. What I'm looking for with the range question, is how to build the formula correctly, and not have to go back in and edit it each time the number of rows increases (very very frequent) - That may work with a dynamic range, but that's secondary to getting the formula to pull the right information, nothing more, nothing less. Would a dynamic range, when created on a table with a different number of rows (identical columns though) be copy/paste suitable for the other tables? Or would each need edited? "T. Valko" wrote: Try something like this for multiple conditions. I like to use cells to hold the criteria. A1 = Group payment B1 = SoCal Airways =SUMPRODUCT(--(P2:P100=A1),--(L1:L100=B1),N1:N100) As far as the size of the data range increasing over time, just increase the size of the referenced ranges: =SUMPRODUCT(--(P2:P1000=A1),--(L1:L1000=B1),N1:N1000) Or, use dynamic ranges: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP |
#8
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
Yes, using '07, I've got rows 1-1048576
Hahah... SUMIFS would be more efficient, huh? That's what I thought comparing the attempts with nested =SUM(IF(...)) to =SUMPRODUCT I'll have to try it out another night, too many numbers, can't see straight anymore. =) That, and the .xml feed isn't updating properly right now... endless "hourglass" with Excel when trying on the BIG page. Thanks again for all the help thus far. It's certainly helped me get 5 steps closer to where I need the workbook to be, functionality-wise. "T. Valko" wrote: If you're referencing 99,999 rows then you must be using Excel 2007. You can use the SUMIFS function which is much more efficient than SUMPRODUCT. =SUMIFS(N:N,P:P,strings!A21,S:S,strings!A44) This also solves the increasing range problem. You can reference the entire column and SUMIFS will only calculate the actual used range. -- Biff Microsoft Excel MVP |
#9
|
|||
|
|||
Totalling seperate cells from a HLOOKUP function
You're welcome. Good luck!
-- Biff Microsoft Excel MVP "Crashin" wrote in message ... Yes, using '07, I've got rows 1-1048576 Hahah... SUMIFS would be more efficient, huh? That's what I thought comparing the attempts with nested =SUM(IF(...)) to =SUMPRODUCT I'll have to try it out another night, too many numbers, can't see straight anymore. =) That, and the .xml feed isn't updating properly right now... endless "hourglass" with Excel when trying on the BIG page. Thanks again for all the help thus far. It's certainly helped me get 5 steps closer to where I need the workbook to be, functionality-wise. "T. Valko" wrote: If you're referencing 99,999 rows then you must be using Excel 2007. You can use the SUMIFS function which is much more efficient than SUMPRODUCT. =SUMIFS(N:N,P:P,strings!A21,S:S,strings!A44) This also solves the increasing range problem. You can reference the entire column and SUMIFS will only calculate the actual used range. -- Biff Microsoft Excel MVP |
Thread Tools | |
Display Modes | |
|
|