Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding sums from different worksheets | Excel Worksheet Functions | |||
Finding a value by adding certain sums in a range? | Excel Discussion (Misc queries) | |||
adding multiple sums w/ different criteria | Excel Discussion (Misc queries) | |||
Adding sums only in certain colored cells | Excel Worksheet Functions | |||
Adding up Sums | Excel Worksheet Functions |