I have this as a formula in a cell:
=SUMIF('[2005 Budbook.xls]01XA'!$B$11:$B$17,$K$4:$K$16,'[2005 Budbook.xls]01XA'!$L$11:$L$17)
The criteria I want it to look up is each value in the cells K4:K16. My formula only sums based on the value in K4, it doesn't look in the rest of the range. How would I make this work correctly? Thanks a lot!
Re: sumif - criteria as a range
ok... for a start, your range (first criteria) only selects column B... it needs to select columns B:L,
Second, your criteria (the second argument) is pointing to a range, rather than just to a single number. THis needs to just point 1 cell (e.g. K4)
Try this
And believe it or not, the help file in excel should tell you all of this
James
Re: sumif - criteria as a range
apparently you didn't understand the question..
I want my criteria (the second argument)to be based on a range.. How can I go about this? I want it to sum if it's any of those #'s. Not just 1.
and the help file does not tell me the answer to this question.. Thanks for the reply though..
Re: sumif - criteria as a range
jjst34,
Try:
=SUMIF('[2005 Budbook.xls]01XA'!$B$11:$B$17,$K$4,'[2005 Budbook.xls]01XA'!$L$11:$L$17) +
=SUMIF('[2005 Budbook.xls]01XA'!$B$11:$B$17,$K$5,'[2005 Budbook.xls]01XA'!$L$11:$L$17) + ....
or put a sumif next to the range k4:k16 and create each of sums then add them together.
A.
Re: sumif - criteria as a range
Shark... exactly.....
Enough said.
Re: sumif - criteria as a range
Hi,
Can you try the following example and see if it gives you the result you are looking for. If it does, you can apply the logic with your workbook references. Enter the formula with Ctrl + Shift + Enter to give an array formula:
Enter values in cells A1:A8;
Enter lookup values from column A in cells B1:B2;
Enter values to sum in cells C1:C8;
Enter the following formula to cell D1:
=SUM(IF(ISERROR(MATCH(A1:A8,B1:B2,0))=FALSE,C1:C8,0))
Hope this helps.
Regards,
Batman.
Re: sumif - criteria as a range
batman.. thanks your idea worked great... that control+shift+enter thing is pretty cool..
james.. enough said
Don’t have an account yet? Register yourself now and be a part of our community!
