Multiple Criteria, Multiple Column

[Guest]

I have used the following formula successfuly to solve a multiple criteria
problem.

{=SUM(('Budget Data'!$A$4:$A$801=$B$5)*('Budget
Data'!$B$4:$B$801=VALUE($A28))*'Budget Data'!$D$4:$D$801)}

However I need to bring in some sort of INDEX/MATCHING solution. At the end
of the above where it says $D$4:$D$801.....I need it to look for the correct
column rather than just look at "D" as depending on the month, the result
should be coming from any of columns 1 to 12. This is obviously difficult
with a multiple criteria array formula!

HELP!!!!!

 

[Roger Govier]

Hi

If you use a cell to hold the column letter you wish to take the values
from, I used P1, then

{=SUM(('Budget Data'!$A$4:$A$801=$B$5)*('Budget
Data'!$B$4:$B$801=VALUE($A28))*
INDIRECT('Budget Data'!$P$1&"4:"&'Budget Data'!$P$1&"801"))}

 

[Guest]

Try:

=SUMPRODUCT(('Budget Data'!$A$4:$A$801='Budget Data'!$B$3)*('Budget
Data'!$B$4:$B$801=VALUE('Budget Data'!$A26))*(OFFSET('Budget
Data'!$Y$4,0,MONTH(X1)-1,798,1)))

X1 contains date for required month

Change $Y$4 to range for January data

 

[Guest]

One way to achieve it via OFFSET/MATCH ..

In sheet: Budget Data,

Assume D3:O3 contains the "month" numbers: 1,2,3 ... 12

Then you could try something like this, array-entered**:
=SUM(('Budget Data'!$A$4:$A$801=$B$5)*('Budget
Data'!$B$4:$B$801=VALUE($A28))*OFFSET('Budget
Data'!$C$4:$C$801,,MATCH(A5,'Budget Data'!$D$3:$O$3,0)))

where A5 would house the desired "month" number col to be summed,
ie: 1,2,.. 12

**Press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

If A5 = 1, you'd get the conditional sum for col D in Budget Data.
If A5 = 3, it returns the conditional sum for col F, and so on ..

 

[Bob Phillips]

=SUM(('Budget Data'!$A$4:$A$80=$B$5)*('Budget
Data'!$B$4:$B$80=VALUE($A28))*INDEX('Budget
Data'!$D$4:$O$80,0,MONTH(TODAY())))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Roger,

Ive tried this but its coming out with the "Value" error.

I tried looking at the formula for indirect on its own in a separate cell.
This gave the result "0", whereas I think it should be giving the result of
the cell ref range.

Any ideas>?!

 

[Guest]

Still no luck with this one chaps! They all look promising, any more ideas?

 

[Guest]

Can you send sample w/book?

toppers at NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM

 

[Guest]

If I understand correctly, this worked OK for me:

G1= date (for month) e.g 01/02/07 for February (UK date)

Monthly data starts in Column D i.e January,E=February etc

=SUMPRODUCT(--('Budget Data'!$A$4:$A$801=$B$3),--('Budget
Data'!$B$4:$B$801=VALUE($A28)),(OFFSET('Budget
Data'!$D$4,0,MONTH(G1)-1,798,1)))

 

[Guest]

Thanks..

I managed to get it to work with :-

{=SUM(('Budget Data'!$A$3:$A$806=$B$5)*('Budget
Data'!$B$3:$B$806=VALUE($A28))*OFFSET('Budget Data'!$D$3:$D$806,0,'Criteria
Sheet'!$E$7-1))}