Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif or Sumproduct
Yodas,
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 1s 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif or Sumproduct
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! In article , Jim wrote: Yodas, 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 1s 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif or Sumproduct
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)) "Jim" wrote: Yodas, 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 1s 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF or ... | Excel Worksheet Functions | |||
SumIf or SumProduct or If statements | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions |