2 condition total

=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.

Hi Aladin

That works a treat but what does the -- do

Peter100 said:

Hi Aladin

That works a treat but what does the -- do

Click to expand...

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.

Hi Aladin

Many thanks.

I could do well to attend some of your classes