I know SUMIF function is (range, criteria, sum_range). I notice the sum_range has to be within the same range like B1:B10, and if I change B1:D10, any value in C and D, are NOT added in the result. Is there a way to add C and D into this sum_range?
Thanks.
Hi and welcome to the forum.
One way is to add the SUMIF
=SUMIF(A1:A10,"a",b1:b10)+SUMIF(A1:A10,"a",c1:c10)+.......
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi qdang,
Newer version of Excel (2007 and 2010) have a SumIFS function that may do what you want.
See http://www.excel-examples.com/sumifs.htm for some examples.
Time to upgrade?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Also try:
=SUMPRODUCT((A2:A10="a")*(B2:D10))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
@ NBVC
Do you understand now, why i (almost) always,ask for am example workbook?
My poor English....
I'm attaching an exammple. You can see the cursor is right now in F column in row 4 with the basic SUM function of the three cells. This would be a manual process. What I'm looking for is to create a better than basic like SUMIFS or SUMPRODUCT, as it looks like I have more than 2 criteria.Sum.jpg
Thanks again.
Please upload a sample workbook to work on it. We can not work in a picture..
Something like:
=SUMPRODUCT(--(ISNUMBER(SEARCH("Projected LOE",A10:K16))),(A11:K17))
perhaps?
This looks at the headers to find Projected LOE and sums the values below them.
@NBVC, I didn't quite get your last replied to me.
Per Fortis suggestion, I uploaded the workbook. In the workbook the top section is the summary. My objective is to SUM "projected LOE hours" for Jon Doe, Wayne Smith from Award Fee 1 and Fee2. Right now in the F column row 4 and 5 have the basic SUM, and doing manually could make the user(s) to select the wrong cell and the result is not accurate.
By having SUMPRODUCT to include (or not) with SEARCH, I want the SUMPRODUCT function to have criteria not just "projected LOE hours" but also the names convention in Jon Doe, Wayne Smith, etc..
Thanks.
Assuming the names are in the same relative positions in all 3 tables, try:
=SUMPRODUCT(--(ISNUMBER(SEARCH("Projected LOE",$E$12:$K$17))),($E13:$K18))
copied down.
Note: the offset in range in second array. When you copy down the row numbers also change relatively...
Thanks for the great input.
Excellent .. but some questions for my curiosity. 1) What are these 2 dashes (--) for? 2) The criteria does NOT need to include NAME? 3) Let's say if I want to include the name - could your SUMPRODUCT include the NAME as well and still get the same result?
My formula, as it stands, can't include the name... that would be a bit more complicated... right now, the formula depends on your names being in same relative positions in all 3 tables...
The -- coerces the TRUE/FALSE results yielded by the conditional checks to 1/0 results, respectively, so the math can be applied.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks