# Excel formulas: multiple columns

VNAM75 01:48 23 Dec 2008
Locked

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

=SUMPRODUCT(--(B1:B100="Actual"),--(C1:C100="May"),D1:D100)

For London only

=SUMPRODUCT(--(A1:A100="London"),--(B1:B100="Actual"),--(C1:C100="May"),D1:D100)

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

A1:A100="London"

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

=SUMPRODUCT((B1:B100="Actual")*(C1:C100="May"),D1:D100)

but that is less efficient.

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

Nokia 8 vs Galaxy S8

Best new design books 2017: From vintage infographics to the the psychology of type

iMac 21.5-inch (2017) review

Nokia 8 : design, caractéristiques techniques, date de sortie