• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help - sumif doesn't seem to work

TomT

TomT

New Member
First some background to help visualize what's going on.

1. Building a workbook which will help me build estimates.
2. There are up to 15 'areas' which can be utilized.
3. Each of the areas can be turned On or Off via a checkbox, linked to a 'master' reference
4. Each area is calculated separately (material and labor)
5. There are two holding cells at the bottom of each column for adding purposes.
6. Using sumif(d5:s5,true,p31:s31) works just fine, adding only areas that are On.
7. Using sumif(d5:s5,true,p31:s31) + (d5:s5,true,p32:s32) adds everything, which is not what I want.

If anyone needs to see it in action, I can upload the workbook later. :)

Thanks
Tom
 
Yes, sample file is required for us to help. It sounds like a PivotTable would be perfect for this - you can use slicers to turn things on or off.
 
I've included the workbook. If you go to the 'Takeoff' tab, and then to cell P33, you will see the formula in question. In the area above (the white cells), you'll see where 1s are entered below areas that are Off, which it's still adding in.
 

Attachments

  • estimate_takeoff.xlsx
    47.3 KB · Views: 12
Hi Tom,

I did not see any problem in the file, or if I am unable to see than please expalin:

If I turn Pipe Chase (2nd check box) to TRUE, than then sum is coming 7,63,580 (Q31+Q32). I think this is only you are looking for.

If I turn Final (3rd Check box) ON than the sum is coming for Q31+Q32+R31+R32.

Can you explain why this is wrong?

Regards,
 
Somendra Misra said:
Hi Tom,

I did not see any problem in the file, or if I am unable to see than please expalin:

If I turn Pipe Chase (2nd check box) to TRUE, than then sum is coming 7,63,580 (Q31+Q32). I think this is only you are looking for.

If I turn Final (3rd Check box) ON than the sum is coming for Q31+Q32+R31+R32.

Can you explain why this is wrong?

Regards,
Click to expand...

Thanks for looking...

If you go into the white boxes below 'Final', and changed them to something other than 1, and the checkbox is OFF, the numbers continue to be added in.

Whenever any area is turned ON, the formula at P33 adds everything. Whereas, if you look at P34, it's functioning normally--with only a single ONEIF statement.

EDIT:
Sorry everyone, I spotted the mistake. The SUMIF is working fine, I had messed up my formula for calculating the labor cost summary. Sorry to have monopolized everyone's time.
 
Last edited:
@TomT

See this is what is happenning:

Formula in P34 depends upon P30:AD30. Formula in P30:Ad30 depends upon respective column above them & Labour Hour. Now Respective column will only come in picture if you in them any number above 0 and Labour hour are fixed numbers. So it is working correctly.

Now formula in P33 depends upon P31:AD31 & P32:AD32. Now formula's in row 32 is depeding upon the column above it and Quote name range which is again fix, so this is also not changing, but formula in row 31 depends upon Labour cost, which is formula driven and depends upon P:AC range, if you track back you will find this. So changing numbers in white cells will change Labour cost and in turn change the result of P33.

Kindly check this.

Regards,
 
Somendra,

Thanks for your help. I found where the mistake was occurring, and now it's working beautifully. :) I guess I shouldn't be writing my formulas in the middle of the night. :)

Tom
 
You must log in or register to reply here.
Back
Top