SUMIF with sequential criteria

[steven314]

I some confusion using problem using SUMIF.

In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805).

HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column).

Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?

Thanks.

[rylo]

Hi

This looks confusing. Can you put up a sample file showing your structure, data, and what you would expect to return from your criteria / data combination.


rylo

[steven314]

Hi

This looks confusing. Can you put up a sample file showing your structure, data, and what you would expect to return from your criteria / data combination.


rylo

Thanks Rylo. Attached is a smaller version of my spreadsheet. (The real workbook has 900+ records.)

My goal has changed just a bit. Basically, I want to sum the fields N-Q in the sheet entitled "Affordable Housing," but have the sums be in the sheet entitled "Cumulative." I want to do it with a formula rather than hasten the carpal tunnel syndrome. In the end I want one record for each city.

[MrShorty]

"criteria" could be a reference to a cell instead of a constant string. So in B2 of cumulative put =sumif('affordable...'!$f2:$f59,a2,'affordable....'!n2:n59) (may not be syntactically exact, adjust as needed) then copy and past in the rest of the cumulative cells. The $ before the f fixes that column as the one the range will use, but leaving it off of the n, means that it will also look at o,p,q,... when it sums as it's copied across.

Does that help?

[steven314]

"criteria" could be a reference to a cell instead of a constant string. So in B2 of cumulative put =sumif('affordable...'!$f2:$f59,a2,'affordable....'!n2:n59) (may not be syntactically exact, adjust as needed) then copy and past in the rest of the cumulative cells. The $ before the f fixes that column as the one the range will use, but leaving it off of the n, means that it will also look at o,p,q,... when it sums as it's copied across.

Does that help?

I'll give this a try and report back. Thanks.

[steven314]

I have changed the name of "Affordable Housing" to "Affordable" for the sake of simplicity. I've posted the following formula:

=SUMIF('Affordable'!$F3:$F805,A2,'Affordable'!N3:N805)

(My numbers begin in column 3 and end in column 805.)

However, no matter how I tweak it, it doesn't return a number, only displaying the formula in the cell. Should I be putting spaces somewhere?

[steven314]

Wait, nevermind, I got it, but it doesn't seem to be returning the correct numbers. I'll check back in a few with more details.

[steven314]

So . . . the single quotation marks around the affordable ID ('affordable'! . . . ) keep disappearing. Any idea?

[MrShorty]

Single quotes around a sheet name are only necessary when the sheet name contains certain characters. When you're only using alphanumerics like you are using, the single quotes are unnecessary (correct but unnecessary), so Excel removes them. The formula should still work.

If I've understood correctly, the function is executing, but returning the wrong value. What is it returning? What do you expect?

One thing to look at is to make sure the strings will correctly compare. Depending on where the data came from, sometimes there are extra spaces contained in the string, which cause the comparison to fail. For example, "abcd"=" abcd " will return FALSE, even though they look the same to you.

[daddylonglegs]

If you fix the cell references appropriately then you can put this formula in B2 and then just drag across and down as necessary

=SUMIF(Affordable!$F$3:$F$805,$A2, Affordable!N$3:N$805)

Note $ sign in front of the row numbers so that your row ranges don't change when you copy formula down, and $ in front of $A2 so that formula still refers to column A when you copy across

[steven314]

Closer attention to the syntax and it's all fixed. Thanks for the help!