1. Count based on 2 conditions using Sumproduct formula

Count based on 2 conditions using Sumproduct formula.


Formula:

=SUMPRODUCT((RNG_C=E12)*(RNG_E=F12))

E12 = Cola2
F12 = b

Answer = 3

Top

2. Count based on 2 or more conditions using sumproduct and +0

Count based on 2 or more conditions using sumproduct and +0.


Formula:

=SUMPRODUCT((RNG_A=E12)+0,(RNG_E=F12)+0)

E12 = 313
F12 = a

Answer = 1

Top

3. Count based on 2 or more conditions using sumproduct and *1

Count based on 2 or more conditions using sumproduct and *1.


Formula:

=SUMPRODUCT((RNG_A=E12)*1,(RNG_E=F12)*1)

E12 = 313
F12 = a

Answer = 1

Top

4. Count based on 2 or more conditions using sumproduct and --

Count based on 2 or more conditions using sumproduct and --.


Formula:

=SUMPRODUCT(--(RNG_A=E12),--(RNG_E=F12))

E12 = 313
F12 = a

Answer = 1

Top

5. Count based on 2 or more conditions using sumproduct and *

Count based on 2 or more conditions using sumproduct and *.


Formula:

=SUMPRODUCT((RNG_A=E12)*(RNG_E=F12))

E12 = 313
F12 = a

Answer = 1

Top

6. Count with Or Condition

Count with Or Condition.
This formula count anything in column C that equal to E12 plus anything in Column C that equal F12

Formula:

=SUMPRODUCT((RNG_C=E12)+(RNG_C=F12))

E12 = Cola2
F12 = Pepsi

Answer = 7

Top

7. Count the number of characters in a range

Count the number of characters in a range.
Return the number of characters in all values and text in Range D

Formula:

=SUMPRODUCT(LEN(RNG_D))



Answer = 43

Top

8. Count how many times a charcter in a range

Count how many times a charcter in a range.
Return the number of characters in all values and text in Range D. This is a case sensitive, meaning "d" and "D" is not the same.

Formula:

=SUMPRODUCT(LEN(RNG_D)-LEN(SUBSTITUTE(RNG_D,E12,""))/LEN(E12))

E12 = D

Answer = 4

Top

9. Sum based on two conditions

Sum based on two conditions.


Formula:

=SUMPRODUCT((RNG_C=E12)*(RNG_E=F12),RNG_H)

E12 = Cola2
F12 = b

Answer = 79

Top

10. Sum of 2 or more ranges using Sumproduct with + (plus) sign

Sum of 2 or more ranges using Sumproduct with + (plus) sign.


Formula:

=SUMPRODUCT((RNG_C=E12)+(RNG_E=F12),RNG_H)

E12 = Cola2
F12 = b

Answer = 202

Top

11. Sum of the difference of 2 ranges using Sumproduct with - (minus) sign

Sum of the difference of 2 ranges using Sumproduct with - (minus) sign.


Formula:

=SUMPRODUCT((RNG_C=E12)-(RNG_E=F12),RNG_H)

E12 = Cola2
F12 = b

Answer = 44

Top

12. Sumproduct with Or Condition

Sumproduct with Or Condition.


Formula:

=SUMPRODUCT((RNG_E=E12)+(RNG_E=F12),RNG_H)

E12 = b
F12 = c

Answer = 133

Top

13. Return the aggregate sum of column H minus column F

Return the aggregate sum of column H minus column F.


Formula:

=SUMPRODUCT((RNG_H-RNG_F))



Answer = 282

Top

14. Return the Sum based on 2 conditions, one in column and other in row

Return the Sum based on 2 conditions, one in column and other in row.
One condition is vertical and the other is horizontal. 2 Way Lookup to lookup value at the intersection corresponding to a given row & column values.

Formula:

=SUMPRODUCT((RNG_A=E12)*($A$1:$H$1=F12),$A$2:$H$10)

E12 = 316
F12 = Quantity

Answer = 25

Top

15. Return the Sum based on 2 conditions, one of them is that left 4 letters are "Cola"

Return the Sum based on 2 conditions, one of them is that left 4 letters are "Cola".


Formula:

=SUMPRODUCT((RNG_E=E12)*(LEFT(RNG_C,4)=F12),RNG_H)

E12 = a
F12 = Cola

Answer = 49

Top

16. Find the Last Item in an Excel List

Find the Last Item in an Excel List.


Formula:

=INDEX(RNG_D,SUMPRODUCT(MAX((RNG_B=E12)*ROW(RNG_D)))-ROW(RNG_D)+1)

E12 = 4

Answer = John

Top

17. Return the product of 2 arrays, one of which is a binary value

Return the product of 2 arrays, one of which is a binary value.
Return the product of 2 arrays, one of which is a binary value composed of 1 and 0 only.
The formula actually divides the binary value to 10 different values, each is 1 or 0.
What we get is: 1X5 + 0X12+0*27+1X44 and so on.

Formula:

=SUMPRODUCT((VALUE(MID(E12,ROW(RNG_H),1)))*(RNG_H))

E12 = 1001011001

Answer = 218

Top