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.

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

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?