Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I would like help with the following problem:
I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help |
#2
![]() |
|||
|
|||
![]()
Assuming your names are in column A, use an array formula like
=SUM(OFFSET(A1,MATCH("Jim",A1:A10,0)-1,0,1,3)) Change the final '3' in the formula to the number of columns you want to sum. Since this is an array formula, you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces {}. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ram" wrote in message ... I would like help with the following problem: I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help |
#3
![]() |
|||
|
|||
![]()
Try...
=SUMPRODUCT((A2:A10="Jim")*(B2:D10)) or =SUM(INDEX(B2:D10,MATCH("Jim",A2:A10,0),0)) Hope this helps! In article , ram wrote: I would like help with the following problem: I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help |
#4
![]() |
|||
|
|||
![]()
Thanks Chip This works great
"Chip Pearson" wrote: Assuming your names are in column A, use an array formula like =SUM(OFFSET(A1,MATCH("Jim",A1:A10,0)-1,0,1,3)) Change the final '3' in the formula to the number of columns you want to sum. Since this is an array formula, you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces {}. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ram" wrote in message ... I would like help with the following problem: I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help |
#5
![]() |
|||
|
|||
![]()
Thanks Domenic this works great
"Domenic" wrote: Try... =SUMPRODUCT((A2:A10="Jim")*(B2:D10)) or =SUM(INDEX(B2:D10,MATCH("Jim",A2:A10,0),0)) Hope this helps! In article , ram wrote: I would like help with the following problem: I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help |
#6
![]() |
|||
|
|||
![]()
I am having a similar dilemma. I would like to use sumif all within the same
colum: Specifically, I'd like to compare a value (F5) to another (10), and if it is greater, then to sum cells F15 and F16. I cannot get the sum_range to sum, i.e. F15 and F16. The output is just F15. Is this because SUMIF can't be used within a single column? Must the ranges be parallel to one another? Also, can I use a cell reference as a criteria? Thanks, Michelle "Chip Pearson" wrote: Assuming your names are in column A, use an array formula like =SUM(OFFSET(A1,MATCH("Jim",A1:A10,0)-1,0,1,3)) Change the final '3' in the formula to the number of columns you want to sum. Since this is an array formula, you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces {}. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ram" wrote in message ... I would like help with the following problem: I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
Dynamic sumif function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |