excel SUMPRODUCT function Using SUMPRODUCT with boolean arrays

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

Consider the following ranges A1:A3 and B1:B3 as below

enter image description here

=SUMPRODUCT(--(A1:A3="c"),B1:B3)

This will first manipulate (A1:A3="c") into the following array

A1="c" = FALSE
A2="c" = FALSE
A3="c" = TRUE

Then apply the -- operator which converts TRUE and FALSE into 1 and 0, respectively. So the array becomes

--FALSE = 0
--FALSE = 0
--TRUE  = 1

Then the SUMPRODUCT formula completes as in the simple numeric case. Returning 6 in this example

0*4 = 0
0*5 = 0
1*6 = 6

Note: this is the equivalent of a SUMIF function



Got any excel Question?