variation on SUMIF funciton

[Gregg88]

I have a spreadsheet of the form:

Household Sales
1 1
1 0
1 1
2 0
2 0
3 1
3 1
3 1
etc, etc,

I am trying to find the total number of sales for each specific household
and as my spreadsheet is very large i'd prefer not to do it manually. I have
tried using a SUMIF function, but I can't manipulate the 'criteria' section
of the function such that I can set it equal to the value of a specific cell
- I can only seem to manually enter in the specific household identity
number.

Is there anyway to alter the SUMIF function (or indeed do it a different
way) that will allow me to do this calculation quickly.

Thanks

 

[Shane Devenshire]

Hi,

Assume your list is in A1:B1000

in cell C1 enter the household you want to count then
=SUMIF(A1:A1000,C1,B1:B1000)

 

[Max]

Try a pivot. Fast n easy. Gives you BOTH the uniques list of households and
the "sum of sales" for each in a matter of seconds.

Some easy steps to create the pivot (in xl2003):
Select any cell within your table.
Click Data > Pivot table. Click Next>Next.
In step 3, click Layout, then:
Drag n drop "Household" in ROW area
Drag n drop "Sales" in DATA area
(It'll appear as "Sum of Sales")
Click OK > Finish. Done.

Hop over to the pivot sheet (just to the left) where you'd find the desired
results, ie the uniques list of households and the corresponding "sum of
sales" for each household.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[John]

Hi Gregg
I tried these 2 formula and both work fine.
=SUMIF(F38:F45,1,G38:G45)
=SUMPRODUCT(--(F38:F45=1),G38:G45)
Adjust range to your need.
HTH
John

 

[T. Valko]

Even easier than a pivot table, use the Subtotal feature.

If your data is sorted or grouped together as is shown in the sample...

Select the column header "Household".
Goto the menu Data>Subtotals
At each change in: Household
Use function: Sum
Add subtotal to: Sales
OK