Sumif or Sumproduct

[Guest]

Yoda’s,

Thanks for your help.

On sheet one I have data from a report. In column A it lists store numbers.
In column B it lists either a 1 or a 2.

A B
101 1
102 1
103 2
101 2
102 2
103 2
101 2
102 2
103 1
101 2
102 2
103 2
101 1
102 2


On sheet two I have the store number listed and would like to sum all the
1’s if it matches the store number.

A B C
1's 2's
Store 101 2 3
Store 102 1 4
Store 103 1 3


Thanks for the help.

 

[Bernard Liengme]

In B1:C1 the numbers 1 and 2 (could go on and on, eg 3 in D1, 4 in E1)
In A2:A4 the store numbers 101, 102, 103 (could go on and on, eg 104 in A5)
In B2 use
=SUMPRODUCT(--(Sheet1!$A$1:$A$14=Sheet2!$A2),--(Sheet1!$B$1:$B$14=B$1))
Copy across to C2 and down to row 4
The $ signs make this absolute to allow copying; the doube unitary negations
convert FALSE/TRUE to 0/1 to allow Excel to do the arithmetic
SUMIF not suitable here as you have 2 criteria
best wishes

 

[Domenic]

Assumptions:

Sheet1!A1:B14 contains your data

Sheet2!A2:A4 contains 101, 102, and 103

Sheet2!B1:C1 contains 1 and 2

Formula:

Sheet2!B2, copied across and down:

=SUMPRODUCT(--(Sheet1!$A$1:$A$14=$A2),--(Sheet1!$B$1:$B$14=B$1))

Hope this helps!

 

[Guest]

Assuming your results table starts in A1:
Enter the following in B2, then copy down, then copy across. (or across
then down if you prefer)



=SUMPRODUCT((Sheet1!$A$2:$A$15=Sheet2!$A2)*(Sheet1!$B$2:$B$15=Sheet2!B$1))