Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF
Hi boys and girls
I have 2 colums i need to sort, first on postal number and second weight between 0 and 3 kg Col. A Col.B 0141 3 0145 7 0189 2 0176 1 0188 3 2100 6 2100 2 3350 2 6893 2 0149 12 The colums can be with 10000 rows What i want is : If col.A are between postalnumber 0 and 0999 AND col.B are between 0 and 3 kg i need the sum of all weights between 0 and 3 kg The same function for postalnumber between 1000 and 1999, and between 0 and 3 kg The same function for postalnumber between 2000 and 2999, and between 0 and 3 kg If col.A are between postalnumber 0 and 0999 AND col.B are between 4 and 5 kg i need the sum of all weights between 4 and 5 kg The same function for postalnumber between 1000 and 1999, and between 4 and 5 kg Also need a count of the same, how many 0 to 3 kg there are if postalnumbers are between 0 and 0999 Also need a count of the same, how many 0 to 3 kg there are if postalnumbers are between 1000 and 1999 Hope I explaned it right I basicly need to know how I can set 2 conditions to the SUMIF AND COUNTIF FUNCTIONS The first condition is that the value i col A is between 0 and 0999 (1000 and 1999 and so on) The second is that the value in col B is between 0 and 3 kg (4 and 5 kg, 6and 8 and so on) Hopefull user Rock Just a regular user |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF
sum:
=SUM(IF(($D$1:$D$10000*10)*($D$1:$D$10000*1<=999) *($E$1:$E$100000)* ($E$1:$E$10000<=3);$E$1:$E$10000;)) count: =SUM(IF(($D$1:$D$10000*10)*($D$1:$D$10000*1<=999) *($E$1:$E$100000)* ($E$1:$E$10000<=3);1;)) adjust yr limits relevantly these are array formulae = insert them with CTRL+SHIFT+ENTER HIH |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF
replace ; with ,
sorry |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF
Hi,
You can use a formula or you could use a PivotTable. here is the basic idea for the formula (one question are the zip codes entered as text or as number and then formatted to show leading zeros? that's best) For 4-5 kg and zips 1000 to 2000 For the sum and assuming zips are numbers: =SUMPRODUCT(--(A1:A10000=1000),--(A1:A10000<2000),--(B1:B10000=4),--B1:B10000<=5),B1:B10000) For count =SUMPRODUCT(--(A1:A10000=1000),--(A1:A10000<2000),--(B1:B10000=4),--B1:B10000<=5)) For the sum and assuming zips are text entries =SUMPRODUCT(--(--A1:A10000=1000),--(--A1:A10000<2000),--(B1:B10000=4),--B1:B10000<=5),B1:B10000) For the pivot table: 1. Select all the cells and choose Data, PivotTable and PivotChart Report 2. Click Next, Next and then Layout 3. Put the Zip Code field button in the Row area and then put the Weight field into the Row area below the Zip Code, then add the Weight field to the Data area twice. 4. If the two weight fields say Sum of Weight then double-click one of the Sum of Weight buttons and change the summary calculation to Count and click OK, OK. 5. Click Finish 6. Drag the Data button on top of the word Total in cell D3 or there abouts. 7. Double-click the Zip code button in cell A3 and set the Subtotals to None 8. Highlight all the zip codes between 0 and 999 and choose the command PivotTable, Group & Show Detail, Group 9. Change the name of the group to something like 0-999 10. Double-click the new title. 11. Drag the Zip Code (not zip code 2) field button off the pivot table. 12. Hightlight the weights between 0-2Kg and repeat steps 8-11 (getting rid of Zip) Repeat as necessary to group all your data. If either of these help, please click the Yes button. Cheers, Shane Devenshire "Rockbear" wrote: Hi boys and girls I have 2 colums i need to sort, first on postal number and second weight between 0 and 3 kg Col. A Col.B 0141 3 0145 7 0189 2 0176 1 0188 3 2100 6 2100 2 3350 2 6893 2 0149 12 The colums can be with 10000 rows What i want is : If col.A are between postalnumber 0 and 0999 AND col.B are between 0 and 3 kg i need the sum of all weights between 0 and 3 kg The same function for postalnumber between 1000 and 1999, and between 0 and 3 kg The same function for postalnumber between 2000 and 2999, and between 0 and 3 kg If col.A are between postalnumber 0 and 0999 AND col.B are between 4 and 5 kg i need the sum of all weights between 4 and 5 kg The same function for postalnumber between 1000 and 1999, and between 4 and 5 kg Also need a count of the same, how many 0 to 3 kg there are if postalnumbers are between 0 and 0999 Also need a count of the same, how many 0 to 3 kg there are if postalnumbers are between 1000 and 1999 Hope I explaned it right I basicly need to know how I can set 2 conditions to the SUMIF AND COUNTIF FUNCTIONS The first condition is that the value i col A is between 0 and 0999 (1000 and 1999 and so on) The second is that the value in col B is between 0 and 3 kg (4 and 5 kg, 6and 8 and so on) Hopefull user Rock Just a regular user |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF
Hi Shane
I Had to use pivot, but it worked like a charm, thanks a trillion and I have clicked yes One more question: I have 25 sheets to make this pivot, is there a way to copy the pivot so it use the numbers in the next sheet? or do I need to make the sort and group in every sheet? Rock -- Just a regular user "Shane Devenshire" wrote: Hi, You can use a formula or you could use a PivotTable. here is the basic idea for the formula (one question are the zip codes entered as text or as number and then formatted to show leading zeros? that's best) For 4-5 kg and zips 1000 to 2000 For the sum and assuming zips are numbers: =SUMPRODUCT(--(A1:A10000=1000),--(A1:A10000<2000),--(B1:B10000=4),--B1:B10000<=5),B1:B10000) For count =SUMPRODUCT(--(A1:A10000=1000),--(A1:A10000<2000),--(B1:B10000=4),--B1:B10000<=5)) For the sum and assuming zips are text entries =SUMPRODUCT(--(--A1:A10000=1000),--(--A1:A10000<2000),--(B1:B10000=4),--B1:B10000<=5),B1:B10000) For the pivot table: 1. Select all the cells and choose Data, PivotTable and PivotChart Report 2. Click Next, Next and then Layout 3. Put the Zip Code field button in the Row area and then put the Weight field into the Row area below the Zip Code, then add the Weight field to the Data area twice. 4. If the two weight fields say Sum of Weight then double-click one of the Sum of Weight buttons and change the summary calculation to Count and click OK, OK. 5. Click Finish 6. Drag the Data button on top of the word Total in cell D3 or there abouts. 7. Double-click the Zip code button in cell A3 and set the Subtotals to None 8. Highlight all the zip codes between 0 and 999 and choose the command PivotTable, Group & Show Detail, Group 9. Change the name of the group to something like 0-999 10. Double-click the new title. 11. Drag the Zip Code (not zip code 2) field button off the pivot table. 12. Hightlight the weights between 0-2Kg and repeat steps 8-11 (getting rid of Zip) Repeat as necessary to group all your data. If either of these help, please click the Yes button. Cheers, Shane Devenshire "Rockbear" wrote: Hi boys and girls I have 2 colums i need to sort, first on postal number and second weight between 0 and 3 kg Col. A Col.B 0141 3 0145 7 0189 2 0176 1 0188 3 2100 6 2100 2 3350 2 6893 2 0149 12 The colums can be with 10000 rows What i want is : If col.A are between postalnumber 0 and 0999 AND col.B are between 0 and 3 kg i need the sum of all weights between 0 and 3 kg The same function for postalnumber between 1000 and 1999, and between 0 and 3 kg The same function for postalnumber between 2000 and 2999, and between 0 and 3 kg If col.A are between postalnumber 0 and 0999 AND col.B are between 4 and 5 kg i need the sum of all weights between 4 and 5 kg The same function for postalnumber between 1000 and 1999, and between 4 and 5 kg Also need a count of the same, how many 0 to 3 kg there are if postalnumbers are between 0 and 0999 Also need a count of the same, how many 0 to 3 kg there are if postalnumbers are between 1000 and 1999 Hope I explaned it right I basicly need to know how I can set 2 conditions to the SUMIF AND COUNTIF FUNCTIONS The first condition is that the value i col A is between 0 and 0999 (1000 and 1999 and so on) The second is that the value in col B is between 0 and 3 kg (4 and 5 kg, 6and 8 and so on) Hopefull user Rock Just a regular user |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |