SUMIF with sequential criteria

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.

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

Originally Posted by 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

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.

"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?

Originally Posted by 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?

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

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?