• Security incident: ISF was recently accessed by intruders. Please change your password, and change it anywhere else you used it. Read more

Wow, Conditional sums and counts in Excel are UGLY

I suppose I need to learn VBA, huh? 35 years in computing, and the only flirtations with BASIC were in the mid 70s when there wasn't really anything else other than assembler, and that wasn't too suitable for quick calculation. (Anybody remember FOCAL?)
 
I suppose I need to learn VBA, huh? 35 years in computing, and the only flirtations with BASIC were in the mid 70s when there wasn't really anything else other than assembler, and that wasn't too suitable for quick calculation. (Anybody remember FOCAL?)


Eh? Mid 70s and not really anything else other than Assembler????
Ben, that's nonsense, and if you've really 35 years in computing, you really should know better.

Heck, I started off in 1969 with Fortran IV and Algol 60.
 
Eh? Mid 70s and not really anything else other than Assembler????
Ben, that's nonsense, and if you've really 35 years in computing, you really should know better.

Heck, I started off in 1969 with Fortran IV and Algol 60.

Well, not on the Altair 8800, or the DG Nova or the HP 2000 I had access to.

Now, when I got my first JOB in computing, the IBM 1130 had FORTRAN II and RPG I.
 
Last edited:
RPG I? Wow! The very first Role Playing Game. You got to pretend to be a programmer. ;-)

Seriously though, VBA is ugly. I hate the damned thing and hated having to get to grips with it. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module.
 
RPG I? Wow! The very first Role Playing Game. You got to pretend to be a programmer. ;-)

Seriously though, VBA is ugly awesome. I hate the damned thing love VBA and hated loved having being allowed to get to grips with it bask in its awesomeness. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module. VBA is god and can do everything!

There, I fixed the typos.
 
Seriously though, VBA is ugly. I hate the damned thing and hated having to get to grips with it. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module.
Yep. No doubt about it. VBA is UGLY. But, perhaps not as ugly as conditional sums and counts in Excel?

Alternatively, you could use the .NET Framework, with Visual Studio Tools for Office. But, I still end up using VBA for Excel, 'cause it's so quick and dirty!
 
Yep. No doubt about it. VBA is UGLY. But, perhaps not as ugly as conditional sums and counts in Excel?

Alternatively, you could use the .NET Framework, with Visual Studio Tools for Office. But, I still end up using VBA for Excel, 'cause it's so quick and dirty!

The thing is, VBA is very well integrated into Office, and it does the job. Sometimes you just need something that will get the job done, elegance be damned!

You know, like conditional sums in Excel.
 
This warms the cockles of my heart, because I use this kind of crap every day.

Gee, I'd sure like to count how many employees on this census are both in NY and also male. Sure would be nice if I had a countif function that took two parameters. What's that, Excel? You say I need to use the SUM command in some arcane combination with the IF command and something you call an "array formula." Of course, that makes perfect sense.

It is counterintuitive, but god damn is it beautiful when everything clicks.

Sounds like a SUMPRODUCT to me.....

=SUMPRODUCT((Gender="Male")*(State="NY"))

(where 'Gender' and 'State' are named ranges, just use B2:B20000 etc. if you prefer....)

Hey, maybe I'm preaching to the converted, but SUMPRODUCT is pretty damn powerful.

I suspect it might address the problem in the OP as well, but I can't quite get my head around the file structure.
 
Last edited:
Sounds like a SUMPRODUCT to me.....

=SUMPRODUCT((Gender="Male")*(State="NY"))

(where 'Gender' and 'State' are named ranges, just use B2:B20000 etc. if you prefer....)

Hey, maybe I'm preaching to the converted, but SUMPRODUCT is pretty damn powerful.

I suspect it might address the problem in the OP as well, but I can't quite get my head around the file structure.

I just tried it and it doesn't work. There might be a way to work something out with SUMPRODUCT, but It probably is actually more complicated than just using the array formula with the SUM((blah)*(blah)*(blah)).

SUMPRODUCT is cool, yeah, but I find it to be more of a specific tool than SUM. Maybe that's just me, I dunno.
 
RPG I? Wow! The very first Role Playing Game. You got to pretend to be a programmer. ;-)

Seriously though, VBA is ugly. I hate the damned thing and hated having to get to grips with it. But it was the quickest way to do some things with excel. For preference I process spreadsheets (quite specialized data) with perl and the Win32 module.

If you ever SAW RPG I you would know that it is indeed a pretend programming language!

It is an attempt to set into card representation the sort of things you could plug-board-program an old IBM printing tabulator machine hooked to a collator and reader-punch to do.
 
That it's tightly integrated into Office and is often the quickest and best way to deal with an Excel problem? Of course.

Serious question though it may not sound like it - can you actually get decent work doing purely Office-based solutions? My career has been in 2 massive companies. Just an idle question.
 
Serious question though it may not sound like it - can you actually get decent work doing purely Office-based solutions? My career has been in 2 massive companies. Just an idle question.

In my experience, that would really depend on the scale and complexity of what you're doing. You can actually use a few of the Office products, notably Access, as fairly robust platforms for doing stuff.

Actually, Access (and maybe Excel) can effectively be used as a rapid deployment platform for internal stuff, as long as you're careful about what the users can access (haw, I made a pun) and everyone has the software. It doesn't scale well, but for a small office it's sometimes the quickest, easiest way to do the job.

When I was in the beginning of the second year with the insurance TPA (third-party administrator - we do various things for a bigger insurance company, but we're a pretty small company ourselves), I started working on an Access-based computer underwriting package. It's useful because it can be very quickly updated to fix bugs, is extremely easy to set up, and doesn't require the whole build cycle to deploy if something needs to change.

So I could've done the job in VB.NET or something, but realistically that would've just been more of a pain in the ass than needed. The software is used by around four people (including myself) who do the underwriting here, so it doesn't need to be massively scalable or even that user friendly (although I've done a lot to customize the Access interface to make it behave more like the users expect - for example, hitting CTRL+S saves the record they're working on rather than the form they're viewing).

All told, it works really well. It would scale horribly, and some of the code required to get Access to behave is a little funky (why in the everloving hell did they use banker's rounding?! I had to write a piece of code to freaking round numbers properly), but to the users it's a dream and it was something I could develop part-time, since that isn't my primary job at the company and our IT department was too overworked to come up with something for the underwriting department in a reasonable time frame.

Sorry for the sermon, but the too long; didn't read answer is "yes." Basically. Sometimes. For smaller companies. :)

Also, I made full working versions of checkers (two-player, no AI) in Excel and Tetris in Access. Toot toot!
 
Oh yeah - I learned VBA as a departed colleague had written a fairly big app in it but such a bad data model and "personal" coding that I decided it was quicker to start again. My wife's IT manager for a company and all their work is VBA/SQL server and she gets a lot of CVs saying "I hav lerned Ofice and can do VBA. I want job as senier programmer" and was wondering if they were as delusional in as many ways as I thought.

eta - to be fair she also gets lots of "Career : 20 year down t'pit. 6 1-hour classes in java oop t'night school. Position sought: senior java architect".
 
Last edited:
I think it depends what you define as 'decent work'

I have done a fair bit of Office-based development in one way or another, but it's not been what I would consider programming or even system development.

More like 21st Century Admin than anything...
 
That it's tightly integrated into Office and is often the quickest and best way to deal with an Excel problem? Of course.

Serious question though it may not sound like it - can you actually get decent work doing purely Office-based solutions? My career has been in 2 massive companies. Just an idle question.

As Johnny 5 has said, depends on scale and time, there are many situations where I will rig up a quick and dirty excel soln (not everyone has access installed) smaller companies love it as it is covered by their immediate requirement using existing licenses, and think they can get pretty much anyone to support it (their thoughts not even close to the the truth :p) it can be used for prototyping and workflow testing prior to any major comitment, as a lot of people can explain what they need in spreadsheet terms.

unfortunately (as in a lot of cases outside of tooling up via office,) the attitude of "The test system worked so why should we continue to hire that Stunningly handsome and mentally stable* developer to do that super duper efficient tight nice and tidy code in a dedicated application, lets just run this uncompiled xl app with all the commented out code redundant segments and kitten referances in, until it collapses as it wasn't designed for full deployment then we can blame it all on him when we hire the next developer" seems to prevail

*DISCLAIMER: words shown are for display purposes only, and may not be representative of actual words used
 
Oh yeah - I learned VBA as a departed colleague had written a fairly big app in it but such a bad data model and "personal" coding that I decided it was quicker to start again. My wife's IT manager for a company and all their work is VBA/SQL server and she gets a lot of CVs saying "I hav lerned Ofice and can do VBA. I want job as senier programmer" and was wondering if they were as delusional in as many ways as I thought.

It's a stupid attitude to have, yeah. VBA would be an absolutely horrid place to learn programming in. I've got some experience in other VB flavors (mostly VB.NET), some C++, and some Java from school, and VBA really doesn't seem to offer anything in terms of learning structure or technique.

I think some people don't realize there's a lot more to programming and software development than simply knowing a particular programming language. I mean, just knowing a language doesn't mean you know how to organize code flow, structure data, implement data conventions, or even name things so that a new user can easily figure out what the hell the program is doing.

I think it depends what you define as 'decent work'

I have done a fair bit of Office-based development in one way or another, but it's not been what I would consider programming or even system development.

More like 21st Century Admin than anything...

I guess it depends. What I worked on was definitely programming and development work. It wasn't really complex, high-level stuff, but it involved a significant chunk of code to get everything to function properly, and a lot of design work to create the framework to build off of.

I'd say it was programming/development in the sense that a mathematician might use C to develop a program to solve a complex problem. I used the Access/VBA platform to solve a very specific set of problems for a very specific user base.

When you start to move beyond that level of things (or scale to large numbers of users), it's just too much of a pain in the ass to make Office behave. At a certain point it stops saving time to use Office for rapid deployment and makes more sense to just use something more robust and go for a standard internal deployment model.
 

ISF - Join now!

Every member here is approved by hand. No bots, no spam, just people who care about evidence and honest debate.

Membership is free!

Create your free account

Back
Top Bottom