SUMIF

[Rockbear]

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

 

[Jarek Kujawa]

sum:

=SUM(IF(($D$1:$D$10000*1>0)*($D$1:$D$10000*1<=999)*($E$1:$E$10000>0)*
($E$1:$E$10000<=3);$E$1:$E$10000)

count:

=SUM(IF(($D$1:$D$10000*1>0)*($D$1:$D$10000*1<=999)*($E$1:$E$10000>0)*
($E$1:$E$10000<=3);1)

adjust yr limits relevantly

these are array formulae => insert them with CTRL+SHIFT+ENTER

HIH

 

[Jarek Kujawa]

replace ; with ,

sorry

 

[Shane Devenshire]

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 coun
=SUMPRODUCT(--(A1:A10000>=1000),--(A1:A10000<2000),--(B1:B10000>=4),--B1:B10000<=5))

For the sum and assuming zips are text entrie
=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]

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