sumif - with 2 conditions - rows and columns

[UKMAN]

Hi

SUMIF(Jan!$BY$3:$DL$3,Projects!$C6,Jan!$BZ$263:$BZ$266)

this formula seems to work but only returns the number from BZ263 not the
sum of the whole range.

As you see it does access 2 sheets and the first criteria is across columns
the 2nd down a list of rows.

Suggests???

Cheers
UKMAN

 

[Roger Govier]

Hi

Sumif won't work in that way

Try
=SUMPRODUCT((Jan!$BY$3:$DL$3=Projects!$C6)*Jan!$BZ$263:$BZ$266)

But wahat do you expect your result to be?
Supposing your first set of values are 5, 10, 15
Your comparison value of the other sheet is 10
Your column of values is 20,30,40

The above formula would give a result of 90
If however, the first set of numbers were 10, 10, 10 then the result
would be 270

 

[UKMAN]

Roger,

You are a star, this worked great.
All I am doing is collating the man-days and revenue for time spent on an
indiviudal project and the layout of the data is unfortunatly strange to
describe. I didn't think of using sumproduct this way so many thanks.

I'm doing further testing of it and at the moment it works great.

Many thanks.