Summing row ranges based on non-sequential column criteria

Hi All, newbie forum member here but long term "jack of all" and hope I can answer as many questions as I ask :-)

I have a question regarding a spreadsheet task I am working on. (Mention now that Pivot tables are a little mystery to me and I have a feeling people are going to point me in this direction so please go slow).

I am trying to sum a number of non-sequential columns based on criteria from another column. I have managed to make it work on one row only, but when I try and expand this out to multiple rows (>800), I can't make it work. The formula I have that works on 1 row is below, and hope this explains in more detail.

=IF('Resource Projection'!D40='Plan V Actual'!J6,SUM('Resource Projection'!Q40,'Resource Projection'!S40,'Resource Projection'!U40,'Resource Projection'!W40,'Resource Projection'!Y40,'Resource Projection'!AA40,'Resource Projection'!AC40),0)

Cell J6 is the resource name reference. The above works fine for only one row, but I want to expand the range out to all 800 rows but conditionally sum only based on resource name.

Thanks in advance all for any help you can offer.

Paul

Thanks for the quick reply Jacc. I think I over explained myself. Rather than go into different sheets, I have used the following of an example of what I want to do. I know this does not work, but if you can follow the syntax of where I am trying to go.....

=sumif(D40:D48,"R5",Q40:Q48,S40:S48)

Whereas the one below does work but only for a single sum range. Once I try to add more than one sum range, this falls over and thats what I need to do.

Thanks

=sumif(D40:D48,"R5",Q40:Q48)

Hi all again, I have managed to make the above scenario work as to how I want it now. However, the way I have done it is not pretty. So you guessed it, my next question is this, is there anyway I can make the following statement a little more manageable and usable? Thanks in advance again.

=SUMIF(D40:D48,"R5",Q40:Q48)+SUMIF(D40:D48,"R5",S40:S48)+SUMIF(D40:D48,"R5",U40:U48)+SUMIF(D40:D48,"R5",W40:W48)+SUMIF(D40:D48,"R5",Y40:Y48)+SUMIF(D40:D48,"R5",AA40:AA48)+SUMIF(D40:D48,"R5",AC40:AC48)

Try this:

=SUM(IF(D40:D48="R5",IF(ISNUMBER(Q40:AC48),IF(MOD(COLUMN(Q40:AC48),2)=1,Q40:AC48))))

Press Ctrl+Shift+Enter, not just Enter

Hi Teethless, Tony

Thanks for the reply.

The reason for the additional column is for data entry to calculate the fields I am reporting on. Not how I would have done it, but as mentioned, I have inherited this from a previous guy. Legacy data and all that :-)

Given the above, would it still be worth trying your formula Teethless as the column Tony refers to here would have number values rather than text or the like.

Thanks guys

Thanks for the above guys. I have just tried this and seems to work well.

:-) Happy newbie and have some rep. Top job.