Sumif problem...

[Guest]

I am trying to write an excel formula that looks at a range of dates across cells B7:W7 (which represent weeks) and wherever it finds a cell that matches the month quoted as it's column heading (ie Y6 = Jan-04) it must sum the corresponding cells on row 10 (ie B10:W10).

Cells in B7:W7 are formatted to show the day only (ie 4/1/04 is shown as 4) where as Y6 is formatted to show the month and year (ie 4/1/04 = Jan-04)

I've tried the formula below which looks fine as you go thru the "insert function" table, until you enter when it doesn't accept it any of it. Something is wrong with it but I don't know what

Any help would be appreciated

=SUMIF(MONTH(B7:W7),MONTH(Y6),B10:W10

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(MONTH(B7:W7)=MONTH(Y6)),B10:W10)

 

[Bob Phillips]

AFAIK, SUMIF will only take a range, not an array of values, which is what
you are passing when you get the month of the range.

It can be done with SUMPRODUCT as Frank shows, or you could use an array
sum, like so

=SUM(IF(MONTH(B7:W7)=MONTH(Y6),B10:W10,0))

As an array formula ( and it needs to be array, as although SUM works on a
range, MONTH does not, so to get the months for the range you need to use an
array formula) you commit with Ctrl-Shift-Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am trying to write an excel formula that looks at a range of dates

across cells B7:W7 (which represent weeks) and wherever it finds a cell that
matches the month quoted as it's column heading (ie Y6 = Jan-04) it must sum
the corresponding cells on row 10 (ie B10:W10).

Cells in B7:W7 are formatted to show the day only (ie 4/1/04 is shown as

4) where as Y6 is formatted to show the month and year (ie 4/1/04 = Jan-04).

I've tried the formula below which looks fine as you go thru the "insert

function" table, until you enter when it doesn't accept it any of it.
Something is wrong with it but I don't know what?