excel DSUM? 2

[MaffewW]

Can I do this with DSUM or what other functions might I need, can someone point me in the right direction pls, perhaps I need vlookup as well? I hope my examples ok I'm just after a pointer in the right direction, so sheet1 column B should have a formula to sum sheet2 response field where validdate = true and sheet1 column mcode matches sheet 2 column mcode


sheet1

A B
MCode Responses
A 9
B 4


sheet2
MCode ValidDate Responses
A True 4
A True 6
A False 3
B True 1
B False 4

Matt

Brighton, UK

 

[MaffewW]

********EDIT*******



sheet1

A B
MCode Responses
A 10
B 1


sheet2
MCode ValidDate Responses
A True 4
A True 6
A False 3
B True 1
B False 4

Matt

Brighton, UK

 

[SkipVought]

Hi,

Using NamedRanges,

in B2...
[tt]
=SUMPRODUCT((MCode=A2)*(ValidDate=TRUE)*(Responses))
[/tt]


Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[MaffewW]

hmmm cant seem to get it working, just getting #name?

thanks for the pointer will have a play...

Matt

Brighton, UK

 

[SkipVought]

You have to create the named ranges. I did that for clarity. Use the sheet & cell range references.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[MaffewW]

ok cheers understand now, though I'm getting #NUM! now

heres the formula

=SUMPRODUCT((Data!G:G='Weekly Report'!A53)*(Data!B:B=TRUE)*(Data!H:H))

So I've got a test record in WeeklyReport Cell A53. The formula is in B53. So I'm trying to return a sum of column H from the data sheet where the value in columnB on the data sheet is true and the values in Data G:G match A53


Matt

Brighton, UK

 

[KenWright]

You cannot use full column ranges in an array or in SUMPRODUCT. You must reduce the range, even if it is only by one row.

=SUMPRODUCT((Data!G1:G65535='Weekly Report'!A53)*(Data!B1:B65535=TRUE)*(Data!H1:H65535))

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------