Sum multiple columns if a column meets criteria

[Rob Webster]

Hi all.

I'm struggling with a particular formula. not sure if this is possible unless I use VBA.

I have a raw data sheet where construction activities are valued. the value is spread over columns and each column represents the date when the work will be done.

the raw data sheet can have columns that are daily, weekly or monthly. and each line of data represents a particular resource that will be used for carrying out the activity.

each resource has a code an that code is used by the summary to work out the total in a particular month. The summary is always monthly but the day summarised in the month is not always consistent. i.e its not always the last day of the month.

I've attached a spread sheet to show you in better detail. which has 2 mock pages representing what I need to do. I've colour coded the cells for ease of showing the expected result of the formula.

I've tried SumIf but doesn't seem to be able to sum up more than 1 column of figures and also sum(if( .... in an array, but all only add up one column of figures.

thanking any one in advance that can help.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[bosco_yip]

Try......

"Summary sheet" D9, copy across and down :

=SUMPRODUCT((Data!$A$7:$A$22=$A9)*(Data!$D$4:$T$4>=D$5)*(Data!$D$5:$T$5=D$6),Data!$D$7:$T$22)

Regards
Bosco

 

[Rob Webster]

Thanks very much to both of you for your time. and the speed you replied. I've been trying for the last few hours to get something to work

Bosco your's works perfectly. Narayan yours trips up in column H and I. I'm not sure what its doing there.

I really need to get to grips with sumproduct, I've only used it in simple ways so far with no more than 2 ranges

your help is very much appreciated.

 

[NARAYANK991]

Hi ,

My mistake ; both the checks refer to the same row of dates in the Data tab ; the first reference should be to :

Data!$D$4:$T$4

instead of :

Data!$D$5:$T$5

Narayan

 

[Rob Webster]

I'm tormenting myself now about how those two things work - but that's better than tormenting myself with how to get to the answer

if an argument = true, does it give a multiplier of 1?

 

[NARAYANK991]

Hi ,

Yes ; when Booleans ( TRUE / FALSE ) values are passed through an arithmetic operation , such as + , - , * , / , they are converted to their mathematical values , whereby TRUE equates to 1 and FALSE equates to 0.

Narayan

 

[Rob Webster]

I didn't know that. it has been very helpful speaking to you both. in the past I've used If, True then 1, else 0 to actually tell it to do the obvious! and struggled in the process.

I'm so glad you guys are here, and put the time in to help out.