sum a RANGE from one criteria

Hello,

I have been working with lots of sumif() this morning and feel that I might have got my mind stuck in that method. My sumif()'s are working fine but now i have come to another type of sum that i'd like to do and I am stuck.

Problem:
I would like to check A:A for a given criteria, but the result should be a sum of H:K. Sumif, and sumifs look for a single, or multiple criteria but they don't appear to be able to sum the range (h:k).

Is there a formula that can do this, without it being an array (my sheet will get very large and arrays will considerably slow down performance). I seem to vaguely remember a sumproduct() can do it somehow but as i said i have got my head stuck in sumif mode and can't think of it!

A non-working example is:

Please Login or Register  to view this content.

thanks in advance.

Try this

=sumif(A2,Transactions!$A$2:$A$23000,Transactions!$h$2:$h$23000) + sumif(A2,Transactions!$A$2:$A$23000,Transactions!$k$2:$k$23000)

Hello,

Thanks for the prompt reply.

That would work, but in fact the columns that i need to show the sum of are H,I,J,K and this would result in a very long formula. Since the formula is to be repeated in 100 rows i fear that this will affect the speed of the sheet.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Please Login or Register  to view this content.

Well rather than posted a sample workbook I will show you what the formula would look like if i just used sumif().

Is there a way to contract this?

Rough description: The formula first looks at the total stock booked into my shop, subtracting the total number of sales. Then, in () brackets it tells me the total stock available in various storage locations - amounts which are recorded in columns H,I,J,K.

I cannot help out unless I see how the data is placed..

Please try and attach a sample.

Jason.b75 i didn't see your post.

Thank you, i will try it now

Ok, I have tried the formula in post #5 and it only shows the value in colH. It does not sum up the values in col H:K

As i mentioned, i think there is a way to use sumproduct. Something like...

Please Login or Register  to view this content.

this doesn't work though, but i might be using the formula in a way that is clouded by my use of sumif all morning :P

Jason you have been unbelievably helpful here!

That formula works, but you have also shown me some information on how to make my sheets work faster in the future AND you have shown me a new way to use the offset function!

I take it that {0,1,2,3} is asking the formula to loop a few times? This is going to be immensely useful to me as i currently have to use some very long formulas where this one would do it quicker.

Huge thanks.

A contraction of the formula i had, or another method that is shorter is what i was looking for.

I was under the impression that a longer formula would take more processing power but i'm glad to see i was wrong about that.

Unfortunately, both of your formulas don't work. The offset one is still only summing colH values and the sumproduct is giving a #N/A. I'm in the process of combing my data to make sure it is in the correct formatting at the moment.

To get around it, I am going to create an =sum(h2:k2) and drag it down to row 23000 on the transactions sheet, then do a single sumif() to get that columns figure in the worksheet i'm using.

It's not clever, but i works :S

To get around it, I am going to create an =sum(h2:k2) and drag it down to row 23000 on the transactions sheet, then do a single sumif() to get that columns figure in the worksheet i'm using.

It's not clever, but i works :S

Anything non numeric (text, errors, of formulas that return blanks) in the sum range, H2:K23000 will cause the sumproduct to fail.

Not sure why the offset version is failing though, it works fine for me on a simple test sheet.

Have you edited the formula in any way in order to match the layout of your real data?

edit:-

Have you tried evaluating the formula?

Go to the formulas tab on the excel ribbon, then click 'Evaluate formula'

On the popup box, click evaluate and the formula in the window will show errors (1 for each column),click evaluate again and it should show the totals for the individual columns as an array, SUM({h,i,j,k})

If any of these don't match up as expected then I would guess that you have some data formatted in a way that the formula is not recognising correctly.