2 condition total

[Peter100]

Hi

I wish to total a column of figures if they meet two criteria. ie.

in column A I have a value (1-12) in column B I have A monetary value and in column C it will contain a blank or an X

I want the total of column B if ithe value in A = 1 and the value in C = X

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$100=E2),--($C$2:$C$100=F2),$B$2:$B$100)

where E2 houses a value like 1 and F2 a value like X.

Alternatively, construct a pivot table from your data.

 

[Peter100]

Hi Aladin

That works a treat but what does the -- do

 

[Aladin Akyurek]

Hi Aladin

That works a treat but what does the -- do

Conditionals like

$A$2:$A$100=E2

return an array of logical values like

{FALSE,TRUE,FALSE,...}

and since SumProduct or control+shift+entered formulas need numerical arrays, the -- bit coerces such arrays into arrays of 1's and 0's (1 and 0 are Excel's numerical equivalents of TRUE and FALSE) like

{0,1,0,...}

Another such coercers is: +0. Thus:

=SUMPRODUCT(($A$2:$A$100=E2)+0,($C$2:$C$100=F2)+0,$B$2:$B$100)

would also work.

 

[Peter100]

Hi Aladin

Many thanks.

I could do well to attend some of your classes