Formula for adding sums
 

I have multiple locations in column B ...B2:B1700 Site A, B, C, ...
In column N2:N1700 I have a number 0, 1, 2 3, 4 and so forth
Column B = Site X Column N= 0
Column B = Site X Column N= 0
Column B = Site X Column N= 1
Column B = Site U Column N= 4
Column B = Site U Column N= 1
I need to add the totals of Site X that equal 0, and Site X that equal 1
etc..
Site X has 3 0's, and Site X has 6, 1's ....
From this information I need to make a chart that shows the number per site
--
CMB

Formula for adding sums
 

=Sumproduct(--(B2:B1700="Site X"),--(N2:N1700=0)) will give you the count for
Site X with Number 0.

You can replace "Site X" by a cell address containing "Site X" and 0 by a
cell address containing 0.
So if you have unique SiteXNumber in X1:Y100 or whatever then you can enter
this in Z1
and copy down to get count for all combinaitons
=Sumproduct(--(B$2:B$1700=X1),--(N$2:N$1700=Y1))
--
If you find this post helpful pl. choose "Yes"...


"CMB" wrote:

I have multiple locations in column B ...B2:B1700 Site A, B, C, ...
In column N2:N1700 I have a number 0, 1, 2 3, 4 and so forth
Column B = Site X Column N= 0
Column B = Site X Column N= 0
Column B = Site X Column N= 1
Column B = Site U Column N= 4
Column B = Site U Column N= 1
I need to add the totals of Site X that equal 0, and Site X that equal 1
etc..
Site X has 3 0's, and Site X has 6, 1's ....
From this information I need to make a chart that shows the number per site
--
CMB

Formula for adding sums
 

Assume the unique sites will be listed in O1 down, eg: Site X, Site Y, etc
Then you could use in P1: =SUMIF(B:B,O1,N:N)
Copy down

Suggest that you also tinker with a pivot table as well. Assume the top col
labels for cols B & N a Loc, Amt. In the wizard's Layout step, just drag n
drop Loc in ROW area and Amt in the DATA area for the desired results. The
pivot will give you both the uniques listing of all the sites in Loc and
their corresponding sums in Amt. You could also explore pivot charting
directly from the pivot. Just right-click on any cell in the pivot Pivot
Chart
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---
"CMB" wrote:
I have multiple locations in column B ...B2:B1700 Site A, B, C, ...
In column N2:N1700 I have a number 0, 1, 2 3, 4 and so forth
Column B = Site X Column N= 0
Column B = Site X Column N= 0
Column B = Site X Column N= 1
Column B = Site U Column N= 4
Column B = Site U Column N= 1
I need to add the totals of Site X that equal 0, and Site X that equal 1
etc..
Site X has 3 0's, and Site X has 6, 1's ....
From this information I need to make a chart that shows the number per site
--
CMB

Formula for adding sums
 

Misread your post. Apologies. Dismiss the SUMIF. But here's how you can use
the pivot to get the desired results: Assume the top col labels for cols B &
N a Loc, Amt. In the wizard's Layout step, just drag n drop Loc & Amt into
ROW area, one below the other, then double-click on Loc to set Subtotals to
None. And drag n drop Loc in the DATA area (it'll show as Count) Click OK
Finish for the desired results. The pivot will give you both the uniques
listing of all the Site-Amt combos in the ROW area, and the corresponding
Counts. You could also explore pivot charting directly from the pivot. Just
right-click on any cell in the pivot Pivot Chart
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---