Excel formulas: multiple columns

  VNAM75 01:48 23 Dec 2008

In col A there are regions eg. London, Birmingham
Col B states whether its a Budget or Actual figure
Column C is the month
Column D is income

I need a formula to give me the total Actual income value for May (all regions) and one for London only, also in May. I tried using a combination of sumif and vlookup but I'm stuck! Any help appreciated.

  Zak 08:01 23 Dec 2008

I would use Auto Filter.

I have prepared a mock up, contact me via yellow envelope and I'll send you the file to look at.

  VNAM75 19:27 23 Dec 2008

Thanks Zak, but I'm looking for a formula solution so the calculation is automated.

  VoG II 19:39 23 Dec 2008

For all regions


For London only


Change the row ranges to suit but note that you can't use whole column references like A:A except in Excel 2007.

The -- are two consecutive minus signs.

  VNAM75 20:08 23 Dec 2008

Thanks VoG™, both formulas worked. Just what I needed.

Just out of interest, why do you need 2 minus's?

  VoG II 20:16 23 Dec 2008

For example


generates an array of 100 TRUE or FALSE values. SUMPRODUCT needs numbers to work with so we need to coerce the values from TRUE/FALSE to 1/0 and double negation is the most efficient way to do that.

You could also use


but that is less efficient.

click here and scroll down to the answer by Aladin.

  VNAM75 20:37 23 Dec 2008

I think I know what you mean, thanks!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Amazon Echo: What’s the best Alexa speaker?

Kano Computer Kit Complete review: A fun DIY 'laptop' that teaches kids to code

Best pro photo editors for Mac 2018

TV & streaming : comment regarder les Jeux olympiques d’hiver 2018 ?