View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default Sumif or Sumproduct

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim" wrote in message
...
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.