How to sum Values in a column even with blank cells untill another column has a blank cell

[ShaunG]

Excel.png

Hi Guys,

thanks to google i have been able to work out formulas from scratch but im stuck on this one, tried multiple formulas with no luck.

what i'm trying to do is -

enter or select an area / Joinery Name in column B which is conditionally formatted

enter different data in column E to N to get a total $ in column O

the above is working beautifully, now my problem is i want all the values in column O to be summed up in column P where it is formatted so where the Kitchen at the top is would have an amount of $1530.00. but only sum the values between the formatting cells.

any help would be much appreciated.

[AliGW]

Welcome to the forums!

Sorry, Shaun, but I don't get it. What is the logic for results appearing in P20, P21 and P24? Why are these values in the line above the other corresponding values? What is the ultimate purpose of column P?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[ShaunG]

thanks ALIGW,

I thought it might of been a bit confusing.

hopefully this attachment comes through.

the top example - Kitchen is how i would to set it up which gives me the total of all the bits for the Kitchen alone.

the other examples is how i have it set up at the moment.

ideally if it sums the total at the top in the formatted cell untill it reaches the next formatted cell would be great

my above examples are in regards to this attached file as well not the 1st attachment i sent through.

[AliGW]

I understand what you want in the blue formatted areas, however, Excel cannot use formatting in a formula, so we will need to work around this. What I don't understand are the other numbers in blue in that column that are not on the shaded blue row - these seem arbritary to me, so you are going to need to explain why they are there and why they are offset,

[ShaunG]

yeah that was my first thought to try and use formatting in a formula but understand now it cant be used. the way i have it set up is that the formula totals all the costs until there is a blank cell, so if i have a blank cell in the column O it does a total in column P.... i have offset the total so it be in line with the area/joinery row which is clearer to read.

im trying to work out a formula so the total in column P sums up the cells in column O until the next cell in column B has text in it, if that makes sense.

[AliGW]

Yes, sort of. Let me have another shufty!

EDIT: There are inconsistencies - look at the kitchen section - there's a gap between over head (sic) units and pantry, but no total in the righthand column. Why?

[ShaunG]

my apologies, I deleted the formula's out of the column for Kitchen to mock up exactly what i'm trying to achieve, the other sections still have the original formula

[AliGW]

OK, I'm getting confused! Let's try again. Please attach a copy of the file with a mock-up of just what you want in column P. Do this manually.

[ShaunG]

OK, I'm getting confused! Let's try again. Please attach a copy of the file with a mock-up of just what you want in column P. Do this manually.

Ohh ok sorry, I will as soon as I can. Thanks

[AliGW]

I shall have a look once you've provided this.

[ShaunG]

hopefully this is what you are after

[ShaunG]

this attached file is a mock up of exactly what I'm looking for

[AliGW]

Yes, thanks. In P18 copied down:

=IF(B18<>"",SUM(OFFSET($O18,0,0,MATCH("*",$B19:$B$53,0)+1)),"")

Adjust the reference in res for the maximum number of rows you might need.

[ShaunG]

Yes, thanks. In P18 copied down:

=IF(B18<>"",SUM(OFFSET($O18,0,0,MATCH("*",$B19:$B$53,0)+1)),"")

Adjust the reference in res for the maximum number of rows you might need.

Fantastic work and thankyou..... Works beautifully!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AliGW]

You are very welcome - so glad we got there in the end!

[ShaunG]

You are very welcome - so glad we got there in the end!

haha sure did, 1 thing that is coming up i didn't notice last night, if there is only 1 value in the B column like Kitchen and not anything underneath im getting N/A in the P column. as soon as put a value in B column underneath like Bathroom The Formula works great.

any ideas on being able to get the formula to work still if there is only 1 value in the B column like Kitchen?