Need a little help with a formula

by Morten_Reitoft Nov 26, 2010 2:55AM PST

Dear fellow Numbers Users..

I have a table with zip codes (Column A) and in the same table I have a list of house holds (Column B).

I would like to calculate a sum of house holds based on two fields describing Zip Codes FROM to Zip Codes TO.. I.e. I have a zip code 4000 to 4100. In this range there is maybe 6-7 zip codes with different number of house holds...

I can't figure out how to make that look up and sum.. I have tried to use Sum.if - but apparently I am not clever enough...!

Any suggestions?

Thank in advance,

Morten (From Denmark... reason for my bad English!)

Discussion is locked

- Collapse -

Re: formula

by Kees_B Forum moderator Nov 26, 2010 3:12AM PST

If you put that table in an Access database, it's a trivial task to make a screen with 2 fields (zip_code from, zip_code_to) and a button executing the following query:
SELECT SUM (households) FROM mytable WHERE zipcode>=zip_code_form AND zipcode<=zip_code_to and put the result in a display field (the technique is to the DSUM-function.

But it can be done in Excel also.

http://www.ozgrid.com/Excel/sum-if.htm tells why you can't use SUMIF (because it uses multiple criteria), but gives DSUM as an alternative. That should work.

Kees

- Collapse -

Poster appears to be using Numbers and not Excel

by mrmacfixit Forum moderator Nov 26, 2010 4:11AM PST

and as far as I can see there is not a DSUM function in Numbers.

P

- Collapse -

Sorry P, My fault totally.

by Kees_B Forum moderator Nov 26, 2010 4:16AM PST

I didn't read the first line. Nor the name of the forum. Just saw the question popup in the real-time listing.

All I saw was sum.if or sumif. And that looked like Excel. Probably it's even available in Office for the Mac. But it's a big step of course to switch to another product just because one thing can't be done.

Kees

Need a little help with a formula

CNET Forums

Other Forums

Forum Info