Trying to have sumif, sum by the row instead of the column
 

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

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

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

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

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

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