Is there a way to calculate the sum to $150. Sum if doesn’t work assuming Col A is only counted in 1 cell. Prefer not to change the layout of column A-C. . Thanks

Post Photo
like
Posting as :
works at
You are currently posting as works at

And you’re not going to tell us what the workaround is, OP? You are a horrible person.

P.S. never ever merge cells for any reason.

likefunny

Get an intern to do it

like

Merging cells is not very friendly for manipulating data. Others have provided workarounds but I tend to repeat the name assets in row if needed rather than merging to span several rows.

like

Sum(c2:c6)... what needs to be conditional

like

Merged cells is a common and unnecessary pain in the ass. Having a giant column to the left to identify “Assets” is also horrible formatting. Either use a section header at the beginning and then indent or repeat your identifier when you can’t see all the info on one page

like

Sigh! I thought this was supposed to be called Excel Geniuses?? I’m about to make everyones day! OP, getting what you want is completely doable! The assumptions required for this specific formula I whipped up are 1) There are no repeating descriptions in the column that contains merged cells 2) The order of the list you want to get the sums for is in the exact same order as it appears in the column of merged cells 3) That you are only working with one column of amounts and not two.

Post Photo
likefunny

Assuming this format repeats and A is not the same in the next set? New column and just use an if looking for blanks in column a, if not blank +1

like

Why not change the layout of column A? If in your population it is merged like that you can highlight all of column A, unmerge, the go to find & select -> go to special... -> blanks -> ok then type =A1 then ctrl+enter. And then you can use the sumif formula you posted

like

To add, then you would have a column that essentially acts as an identifier on each row, you could remove duplicates and build a lookup table and could lookup against the table in your sumif so your output is cleaner.

like

Sum in column B with the first value fixed sum($a1:a1). Then an if in column C

like

I agree with others that you should change your table and not use merged cells or add a helper column that you can hide. But if you must do what you are doing then there is a hack. You have to use format painter and not the merged cell on your ribbon. Just create a merged cell template somewhere in your sheet (F3:F6), then use format painter to merge your cells in B3:B6. Thank me later.

Post Photo
like

I don’t want a simple sum column. Consider this to be a massive spreadsheet with many acts

Does column A have to be merged? You can always unmerge so the formula can work, and hide that column because you’ll still have the name “assets” next to the total 🤷🏻‍♀️

Is the source a pivot table?

I did another workaround instead to make it work and move on w life . Sighh :) thanks all

You could use an IF statement instead.

Pwc2 you made my quarter

Related Posts

Hi, what is the hire and fire policy in BAContinuum. Are they laying off people or did in the past?

like

Hi Folks,
I got offer as infrastructure consultant in TW, what type of projects and clients TW have fro DevOps and cloud work? Pune location.

like

Hello! I’m looking for a referral to any account manager/ CSM roles. Please DM if you can provide a referral. Thank you in advance!

like

Anyone at BlackRock willing to connect?

like

Still putting it in the risk log tho - you can’t be too careful

Post Photo
likefunny

I feel like I wouldn't get many tips as a server because I'm a guy, is this really true or am I just making it up?

like

Hi Guys ...
I am looking for a new opportunity/new role .
I have 5.5 years of experience in automation testing.

like

How do you deal with being told you're 'too young'?
I recently moved to the Netherlands on a highly skilled worker visa. I got offered a job in IT as a Systems Engineer which later turned to project engineer that focuses mainly on migrations. I'm getting the minimum salary of around 42k to keep my visa and management has straight up told me that they think I'm too young to be getting this kind of salary.
I have 8 years of relevant experience and have my performance review coming up soon.

like
like

Lets remove some enters and spaces in the code, & run it again :)

Post Photo
like

Impetus Technologies is hiring for L2/L3 #application #supportengineer with experience on #bigdata or #SQL or #snowflake with #linux #scripting

#Experience: 3-8 years

Preferring early joiners.

Interested folks please share cv at 
sonal.mishra@impetus.com

#applicationsupport #productionsupport #bigdata #sql #hadoop #hive #python #rootcauseanalysis #debugging #spark #incidentmanagement

#indore #noida #gurgaon #bangalore #pune #hyderabad #chandigarh #kochi #ahmedabad #jaipur

like

Hello DevOps Engineers,

I have 3+ Support experience and 1.5 as DevOps Engineer. I have worked on Jenkins, Docker, Kubernetes, Terraform and I’m certified in terraform and kubernetes and GCP.
I’m getting an offer from a company as Site Reliability Engineer. Is it good to move their as SRE? Will it look good on resume?

Total Exp: 4.5
CCTC = 2.5 LPA
Offered = 12 fixed

like

Hi! I joined a startup last month and was offered 30,000 stock options with a value of $60,000 with a 4 year vesting period with 1 year cliff. What does this mean? Is it good? It’s the first time I’m getting stock options and I haven’t yet been provided the stock options contract (they said it’s coming).

Hi Fishes,
Please help me decide. I have an offer
Tech Mahindra . YoE 12, Data Modeling, Band offered : P1
I am looking for work life balance , job security and long term engagement. Heard alot about Tech M firing people with higher pay.
Would really appreciate any help

like

Does one actually need a college degree to do this job? Explain your answer.

like

what's the mat leave policy in your firm? And other nice perks?

like

Of topic :- I have to relocate to Hyderabad any recommendation for hotel near Gachibowli hitech city area… i have to relocate with my wife and a year old kid

How is venkatesh graffiti glover society in Keshav Nagar?

Hello All!
I have a bachelor’s degree in Computer Science and have a 1 yr experience in tech industry. Earlier I have worked as a software associate for 6 months and currently working as a support engineer. Both positions I have been were on contract. I am eagerly looking for full-time job roles. I have OPT work authorisation, so I’m not eligible to apply for many entry level roles. I would appreciate it if you could refer me for entry level tech roles in your company.
Email: saee.a.gore@gmail.com

like

Best recruitment agencies / headhunters for those looking to get into tech (product management, strategy) at a junior level?

like

Additional Posts in Excel Genius

Hello! Is there any VBA coding to clear all column filters in a pivot table? I see how to remove all fields... But I want to keep all the fields and just clear all the filters. Thanks!

like

Why does my excel always freeze when I copy a cell?

like

Will index match fall by the way side with the Advent of xlookup?

like

Been in consulting for over a year and wondering what other formulas other than index/match and vmatch is useful.

Is there any way to compare multiple index match formulas. Looking to find a match across three different tables on three different tabs. Most I’ve seen is using if error to compare 2.

like

I’m experiencing a delay when I switch between different workbooks where it would take at least 5 seconds until I can click into the new workbook. I’m losing productivity especially when I’m doing a copy and paste task between different spreadsheets. Anyone else experienced this before and know any potential solutions?

like

Hey Geniuses! I apologize in advance if this question has been asked before, I'm new here and just trying to learn as much as i can. Does anyone know a great source to learn Macros for beginners. I would like to create one for work. I appreciate it

like

I have multiple sheets with same columns that I want to combine in a single sheet within the same workbook without having to copy paste . They all have same column names and each one has a header row. How to do this ?

likesmart

Noob here:(
Lead wants me to limit values in 5 columns - achieved
Now she wants me to implement - If 1st column is filled and others' are empty, raise an error. Tried ISBLANK but it removes drop-down

like

Hello again fishies...

Column D in tab 1 and tab 2 is a drop down list which has 6 items.

In tab 3 how can I return the Cells in Column a b and c (tab 1 and 2) when the value in cell d(tab 1 and 2) is one of the 6 items selected

like

Change my mind

Post Photo
likefunny

Need help tabulating survey results. Each row is a respondent, but the question asks “choose all that apply” resulting in several columns associated with that one question. How can I get a distribution for which answers were chosen among the top 3?

like

Hey, I have been tasked with teaching some of my coworkers excel from the very basics to more advanced. Does anyone have recommendations on the skills to teach them about? Thank you all in advance!

helpful

How can I copy a formula from one sheet to another?i tried the simple Ctrl+C, Ctrl-V, but the formula refers to the old sheet.

like

SUMIFS wildcard question: is there a way to pick up values based on the cell containing a wild card? (So not an in formula wildcard)
Example: I have scenario A1, A2, & B1
Some criteria apply to both A1 & A2, and I would like to not duplicate the criteria. So when the user selects scenario A1, I want to pick up criteria A* & A1, but not A2. For selecting scenario A2, I want to pick up criteria A* & A2, but not A1.

like

How do you create a Pareto chart in excel? Like I want to identify what percentage of customers contribute to 80% sales. I have data at individual customer level.

like

Do you still use index match? Is it (Always) Possible to switch to xlookup?

like

I use both excel and google sheets, and love them both for different reasons, but they don't agree on which direction decimal rounding goes and it makes me crazy

Post Photo
likefunny

Just got a new personal laptop. Is there a good way to get Microsoft Office for free? I’m looking for a download rather than the subscription usage

Has anyone ever run into a situation where saving as a .xlsb wasn’t better? What kind of file did you have to where xlsx was advantageous?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal