sumif 1

[ashtonp2]

I am using the sumif formulat in a speadsheet I am creating and wanted to know if there is a way in the sum range I can ask excel to conconcatenate certain columns to create my sum range. I realise I could create the conconatenate formula in another column and then point the sumif funtion to that column but I have a large set of data and various sumif I want to create and was hoping doing it real time would make the spreadsheet smaller rather than have a load of columns with various conconcatenate's.

I have tried using the concatenate function in the sum range and then with in the concatenate use the column function, below is a copy of the formulat I tried to create but this did not work.

=sumif(concatenate(column(h:h),column(i:i)),b4,w:w)

 

[KenWright]

=SUMPRODUCT((H2:H1000&I2:I1000=F2)*(W2:W1000))

Note,
- you cannot use whole column ranges eg H:H
- you cannot include headers in the range of a numerical set of data, in this case column W

You can however combine this with the OFFSET & COUNT functions to create a dynamic range that will expand or contract to include only the range that has data in it.

For more info on SUMPRODUCT, Bob Phillips now has a great page on this here:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------