Saturday, September 7, 2013

yogi_Compute Sum Of Items From A Sheet of Locations And Row By Row List Of Item Numbers In An Open-Ended Set of Columns

                                          Google Spreadsheet   Post  #1361
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 07, 2013
user Ben Sigman (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/rJBy8fPs9j8)
sumif= doesn't SUM correctly... I'm getting the wrong number calculations. what's up with that?
I have the following data and I need to calculate the number of items for each location each time it shows up in the list. The formula was working for a long time, and now it is adding the numbers totally inaccurately...

Basically, it is just a simple sumif on another tab... 

98Los Angeles, CA
32Cabo San Lucas, MX
43Los Angeles, CA
10Las Vegas, NV
43Los Angeles, CA
54Cabo San Lucas, MX
65Los Angeles, CA
43Cabo San Lucas, MX
32San Francisco, CA
32Cabo San Lucas, MX
109Los Angeles, CA
10Las Vegas, NV
43San Francisco, CA
98Cabo San Lucas, MX
32Los Angeles, CA
21Portola Valley, CA
43Los Angeles, CA
21Cabo San Lucas, MX
43Las Vegas, NV
1615Los Angeles, CA
Cabo San Lucas, MX
21Los Angeles, CA
43Las Vegas, NV
65Cabo San Lucas, MX
32Los Angeles, CA
21San Jose, CA
32Los Angeles, CA
76Cabo San Lucas, MX
21Los Angeles, CA
32San Francisco, CA
98Las Vegas, NV
32San Francisco, CA
98Cabo San Lucas, MX
32Los Angeles, CA
21San Jose, CA
1211Cabo San Lucas, MX
32Las Vegas, NV
43Los Angeles, CA
32Cabo San Lucas, MX
43San Francisco, CA
54New York City, NY
54Cabo San Lucas, MX
43Los Angeles, CA
10San Francisco, CA
76Las Vegas, NV
54San Jose, CA
54Las Vegas, NV
32San Francisco, CA
1312Cabo San Lucas, MX
21San Francisco, CA
21Los Angeles, CA
32Cabo San Lucas, MX
87Paris, France
32Mallorca, Spain
43Gothenburg, Sweden
54Las Vegas, NV
32Cabo San Lucas, MX
21San Francisco, CA
32Los Angeles, CA
10San Jose, CA
32Los Angeles, CA
21Las Vegas, NV
10Phoenix, AZ
1413Cabo San Lucas, MX
21Los Angeles, CA
1211Cabo San Lucas, MX
109Los Angeles, CA
32Las Vegas, NV
21San Francisco, CA
65Las Vegas, NV
32Los Angeles, CA
21San Jose, CA
1110Los Angeles, CA
1514Kilamanjaro
43Zanzibar Tanzania
10Nairobi
76Paris


In this case the totals for Las Vegas, NV are actually: 
4837

But they are being calculated as by sumif as:
5846
------------------------------------------------------------------------------------------------