2D SUMIF with both ROW COLUMN criteria

[Sloopy]

Hi Folks,

I have been looking with great interest at the SUMIF postings on the site and wondered if there is an elegant way to deal with both row and column criteria to sum only certain cells which intersect with both row & column criteria.


Let me explain further.

For each row I have contract and resource names along with hours spent by week on a given contract so a contract may appear multiple times as might the resource name.


The hours spent by week columns are headed w/c 09-Aug-10, 16-Aug-10 etc...


My excel query needs to select for a given contract what has been spent to-date so only those rows which have criteria = contract name and columns <=TODAY().


I've tried a number of options but am struggling to make this work effectively.

Any advice would be greatly appreciated.

 

[Hui]

Sloopy

Have you tried using Sumproduct

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

or

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html


you will end up with

=Sumproduct(+1*(Row Range1=x)*(Row Range2=y)*(Col Range=z),(Sum Range))

or something similar

 

[dan_l]

sample data please?

 

[Hui]

Might be better if you post a sample of what you are trying to achieve

 

[dan_l]

Hui always steals my ideas.....

 

[Sloopy]

Thank you for your replies Hui & Dan, and sorry for not getting back to you sooner. I will take a look at the =Sumproduct option, from a data perspective I'm not sure how to upload a file onto the forum but lets say I have the following:


Col A = Contract Number (I have multiple contracts)

Col B = Resource Name (I have multiple resources on a contract)

Col C = Resource Rate

Col D onwards represent week commencing dates (from a given start date)


Underneath the week commencing dates I have the price for that week of a named individual on a specific contract.


I need to keep a running total for a given contract of the price to date and estimate to complete which is essentially: For each Row = Contract Number Sum each column <=TODAY() (and conversely > TODAY() for estimate to complete). Sounds easy?


I can easily SUM a range based on COL A criteria but am struggling to add the date criteria to limit the range to only those columns which apply to the date criteria set.


I will take a look at the Sumproduct recommendation as I haven't used this feature before.


Thanks again for your help.


Sloopy.

 

[Hui]

To post files have a read here

http://chandoo.org/forums/topic/posting-a-sample-workbook


Before today have a look at

=SUMPRODUCT(1*(D1:Z1<=TODAY())*(D2:Z2))


After today have a look at

=SUMPRODUCT(1*(D1:Z1>TODAY())*(D2:Z2))


Change Z to suit your data

D1..Z1 have to be Dates


Have a Read of http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

to assist with your understanding of Sumproduct

 

[Sloopy]

Thanks Hui. I've managed to get a working version by performing a SUMIF based on Contract Number (Col A) and storing this as a new ROW for each contract and then use the date comparison functions to work out the two values (it actually helps to see the contract line details as a summary in any case). I also needed to create a monthly view so have an interim table that calculates the monthly contribution per individual line.


That said I will take a look at your suggestion above and learn more about Sumproduct as it looks hugely powerful.


:0)