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

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)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Gregg88" wrote:

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

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 NextNext.
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
---
"Gregg88" wrote:
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

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

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 DataSubtotals
At each change in: Household
Use function: Sum
Add subtotal to: Sales
OK

--
Biff
Microsoft Excel MVP


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