Range for SUMIF (Excel 2000)

Topic

New Reply

I have a spreadsheet with data for individual dealers.
In “district” columns, I’m calculating district averages with the sums of the raw dealer data. All dealers report one element of the formula (in row 7), but some don’t report the data specific to this formula (in row 38), so I’m using SUMIF to only include data that’s complete.
In this example, the dealer data is in B through H. I is the district column.
=IF(AND(I38″-“,I7>0),I38/(SUMIF(B44:H44,”0″,B7:H7)/$A$2),”-“)

I38 = SUM(B38:H38)
I7 = SUM(B7:H7)
B44 through H44 = IF (B38=”-“, 0,1)…

This works fine, but recently the districts changed. I’d like to just add new district columns to the right of my existing table so I don’t need to change overall structure of all the data.
=IF(AND(AX38”-“,AX7>0),AX38/(SUMIF((B44,D44,K44,L44,M44),”0″,(B7,D7,K7,L7,M7))/$A$2),”-“)

Unfortunately, this generates a #VALUE! error message.
Is there a function I can use to tell Excel to use my list of cell references as a range?

Thanks
Chris

Reply | Quote

Replies

Not really answering your question.
Arrays. I strongly recommend you develop your workbook to run off an array of data that you can simply replace perdiodically.
This way the data can change (in terms of regions etc) but you won’t have to worry (so much) about the flexibility of your workbook.

Perhaps if you post an example I’ll ‘array’ it for you and you can take the Pepsi taste test?

Reply | Quote

I don’t know enough about arrays to know if they’ll be useful or not – but I’m willing to learn.

The gray areas (row 3:25) have the individual dealer data for that month – I have 36 of these “month” spreadsheets – Current Year, Last Year and Year Before
The calculation of the measurement for that month is below the input area (rows 27:59)

I also have 13 – Year to Date sheets (one for everything reported and one YTD as of each month of the year) and 24 Rolling Average sheets (each month of the Current Year and Last Year) that use this data.
In addition, I use a macro to make a Feedback spreadsheet that consolidates the available data for whoever I’m running the report for – I enter the column number for the dealer and the column number for the district and it copies all the data values from each sheet and puts them in a table so the trends can be graphed.
I really don’t want to mess with my column designations…

Can anyone help?
Thanks

Reply | Quote

You’re pretty set about the layout of the summary so I suggest you take a look at using arrays in any future work you do. They offer the advantage that they are VERY flexible – use them whenever you know you’re going to be updating ‘data’ and producing a summary.
We use them in a bank environment. The summaries we produce are very sophisticated (Arrays can be dynamic – I can offer more examples if people are interested)

Consider this a pointer as to how I would have gone about producing summary of the type of data you’re entering. If it feels right – great. If not – sorry I couldn’t help more

Bemused

Reply | Quote

Thank you – I’ve wondered about arrays, but never took the time to try to figure out what they could do for me.
After this example, I think I will be able to make use of them in the future. Possibly I’ll even be able to use them to help me twist the data into the format I need for this particular change to my existing spreadsheet…
I guess I want to go back to my original question though
Is it possible to get SUMIF to do what I want it to do?
If I get a firm “No” – I’ll know it’s time for more drastic modifications…

Thanks
Chris

Reply | Quote

SUM and SUMIF only work on contiguous ranges of cells. So, the simple answer to your question is no. It would be possuble to accomplish what you want to do, by doing the following:

1- Select contiguous ranges of cells and enter formulas in them that refer back to the cells you are working with. For example =B44, then in the next cell =D44, etc.

2- Now, do your SUMIF using these contiguous cells.

3- You can now hide the columns containing the contiguous cells if you want.

Reply | Quote

Thank you Legare
Back to the drawing board…

Chris

Reply | Quote

Thank you Legare
Back to the drawing board…

Chris

Reply | Quote

SUM and SUMIF only work on contiguous ranges of cells. So, the simple answer to your question is no. It would be possuble to accomplish what you want to do, by doing the following:

1- Select contiguous ranges of cells and enter formulas in them that refer back to the cells you are working with. For example =B44, then in the next cell =D44, etc.

2- Now, do your SUMIF using these contiguous cells.

3- You can now hide the columns containing the contiguous cells if you want.

Reply | Quote

Thank you – I’ve wondered about arrays, but never took the time to try to figure out what they could do for me.
After this example, I think I will be able to make use of them in the future. Possibly I’ll even be able to use them to help me twist the data into the format I need for this particular change to my existing spreadsheet…
I guess I want to go back to my original question though
Is it possible to get SUMIF to do what I want it to do?
If I get a firm “No” – I’ll know it’s time for more drastic modifications…

Thanks
Chris

Reply | Quote

You’re pretty set about the layout of the summary so I suggest you take a look at using arrays in any future work you do. They offer the advantage that they are VERY flexible – use them whenever you know you’re going to be updating ‘data’ and producing a summary.
We use them in a bank environment. The summaries we produce are very sophisticated (Arrays can be dynamic – I can offer more examples if people are interested)

Consider this a pointer as to how I would have gone about producing summary of the type of data you’re entering. If it feels right – great. If not – sorry I couldn’t help more

Bemused

Reply | Quote

I don’t know enough about arrays to know if they’ll be useful or not – but I’m willing to learn.

The gray areas (row 3:25) have the individual dealer data for that month – I have 36 of these “month” spreadsheets – Current Year, Last Year and Year Before
The calculation of the measurement for that month is below the input area (rows 27:59)

I also have 13 – Year to Date sheets (one for everything reported and one YTD as of each month of the year) and 24 Rolling Average sheets (each month of the Current Year and Last Year) that use this data.
In addition, I use a macro to make a Feedback spreadsheet that consolidates the available data for whoever I’m running the report for – I enter the column number for the dealer and the column number for the district and it copies all the data values from each sheet and puts them in a table so the trends can be graphed.
I really don’t want to mess with my column designations…

Can anyone help?
Thanks

Reply | Quote

Not really answering your question.
Arrays. I strongly recommend you develop your workbook to run off an array of data that you can simply replace perdiodically.
This way the data can change (in terms of regions etc) but you won’t have to worry (so much) about the flexibility of your workbook.

Perhaps if you post an example I’ll ‘array’ it for you and you can take the Pepsi taste test?

Reply | Quote