Formula for sum of data that doesn't include blank spaces
I am trying to sum up a row of numbers but do not want to include the blank cells in the total.
if 'C5' cell is left blank I do not want to calculate in cell H5 but do wish to calculate the rest of the row in 'I5'.
if 'E5' cell is left blank I do not want to calculate in cell 'I5' but do wish to calculate the rest of the row in 'H5'.
I have tried but am lacking the know-how. please help
attached file for reference
If the cells are blank, they wont be included in the total anyway?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Maybe try at
H3
=(B3*C3+D3*E3+F3*G3)*(C3>0)
I3
=(B3*C3+D3*E3+F3*G3)*(E3>0)
thank you for stating that
Last edited by Por2gal; 07-30-2019 at 05:31 AM.
how can i get an answer highlighted in the appropriate cell whilst leaving the neighbouring cell empty. i have attached an updated coloured coded workbook. thank youOriginally Posted by Bo_Ry
[I5 =SUM(D5*E5)+(F5*G5)] but H5 creates an answer that i want to see left blank due to C5 being blank, i already have the correct answer in I5.
[H4=SUM(D4*E4)+(F4*G4)] but I4 creates an answer that i want left blank due to E4 being blank. i already have the correct answer in H4. whilst keeping the data complete on the table. Thank You
Can you please tell us if the results you have shown are the results you want? If not, what should they be (calculated manually)?
What is the difference between the sum you want in I and that in H? I am not following your logic (which is not clear to me in the attachment - sorry).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
im not sure of the correct terminology used in excel. but i think i have explained to the best of my understanding of my request for the answer of the coloured text in the cells:- new attachment supplied above.
[I5 =SUM(D5*E5)+(F5*G5)] but H5 creates an answer that i want to see left blank due to C5 being blank, i already have the correct answer in I5.
[H4=SUM(D4*E4)+(F4*G4)] but I4 creates an answer that i want left blank due to E4 being blank. i already have the correct answer in H4. whilst keeping the data complete on the table.
Please excuse my limited vocabulary for excel
Last edited by Por2gal; 07-30-2019 at 05:39 AM.
Can you please tell me what results you WANT to see in these cells (numbers, not explanations):
Excel 2016 (Windows) 32 bit
$ - $ - $ 345.68 FALSE
Sheet: Sheet1
Is this what you mean?
In H3 copied down:
=IF(C3="","",SUM(D3*E3)+(F3*G3))
In I3 copied down:
=IF(E3="","",SUM(D3*E3)+(F3*G3))
hi there, i wanted to sum up B4xC4 and F4xG4 into H4... but I wanted to leave I4 blank because E4 has no data in the cell. the same for I5 in red to relate to the sums in red from D5xE5 and F5xG5, in this H5 should be left blank as I want it to relate only to B5xC5 and F5xG5. if C5 is left blank then i would like H5 to be left blank . the 'false' came up when i tried to remedy this myself.. to no avail
So did the formulae I gave you work for you?
unfortunately i have an error, as attached
Are you running a machine with a European locale? Try changing the commas to semi-colons:
=IF(C3="";"";SUM(D3*E3)+(F3*G3))
Last edited by AliGW; 07-30-2019 at 06:24 AM.
thank you so much, that is exactly how i wanted to view my answer,
You're welcome - and thanks for the little animated thank you.
sorry to bother you, if i add data to cell E4, i get the answer in H4 but sums up the exact same answer in I4. how do i collect the sum of B4xC4 and F4xG4 for the answer in H4 plus the sum of D4xE4 and F4xG4 for the answer in I4 so that the answer differs from H4. or should i create a new thread for this question? please and thanks
Can you upload a sample workbook showing the issue? Again, please fill in the answers that you expect manually.
so i have added my request onto the file you sent me. as Atttached. in keeping the format you calculated for me I wanted the option to sum up the total of the row without doubling up on F & G
I'm struggling with your logic here. What on Earth does this mean?
Excel 2016 (Windows) 32 bit
answer answer
Sheet: Sheet1
Why have you shown results in one row?
Excel 2016 (Windows) 32 bit
32 345.68
Sheet: Sheet1
And finally, how should Excel determine when it should calculate FxG?
the first picture shows the summing up of cells. but how do i format this in the formular bar
the summing up of row 7 in H and I, but i would like to exclude FxG if its already been used in conjunction with DxE or BxC. this being that if [DxE+FxG+BxC= total]or [BxC+FxG+DxE=total]. thank you for considering my awkward question
Last edited by Por2gal; 08-01-2019 at 01:27 AM.
OK, so how do we know which side the FxG calculation should be included?
[arrr that is the million dollar question
] if an answer is already summed up in cell 'H' then cell 'I' should only calculate DxE, should it be an 'SUMIF' function or a 'LoOKUP' or an 'ERROR', based on whether DxE has or has not been used. and if i choose to calculate BxC later on the same row: should I use the 'IF' function. I was hoping there would be a conditional formular or function to use based on whether these cells are being used in conjunction with each other or not. i could just manually delete the required calculation in the formular bar each time.?
This is what I feared. I really don't think this can be done: you have to be able to 'teach' Excel the logic within the formula, and if there is no logic, then you can't build a formula. I think you might be able to do it with VBA, but again, there would need to be some logic.
I'm afraid I have reached the end of the road here. Sorry!
Maybe try at
H7
=IF(C7>E7,F7*G7,)+B7*C7
I7
=IF(C7>E7,,F7*G7)+D7*E7
cheers for that; thank you again for all your help, I really appreciate you considering my dilemma.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks