SUMPRODUCT

[xxsalahxx]

Hello
I have a formula that is working perfectly in my database however i wish to expand on it and cant seem to work this out the formula is as below.
=SUMPRODUCT((Data!B:B>='Sheet2'!A10)*(Data!B:B<='Sheet2'!B10)*(Data!G:G='Sheet2!$C$1))

The formula works as follows:
The Data Sheets has multiple dates and im looking at this over months and years, i have added in dates to capture this into Columns A and B on sheet 2
Then im taking from the data sheet column G the name of a place which I have added into Sheet2 cell C1
However I wish to pick up the names of other places which are in cells C2 & C3 on sheet2 and total these all together. is this possible?

I use excel 2007

Thanking in advance for any help

 

[James006]

Hello,
Your request is not extremely clear ...
But, you can always add up several Sumproduct formulas ...

If you want a single formula, just remember for your conditions :
1. the * operator means AND
2. the + operator means OR

 

[xxsalahxx]

Hi, thank you for responding to my question so quickly, this is what ive tried this is my full analysis but does not seem to work, its working perfectly when i just use C1 but when asking to use C1 +C2 +C3 then it returns a zero and there is definately data on the data sheet that should be showing, is there another way?

=SUMPRODUCT(--(Data!$B:$B>=A10)*(Data!$B:$B<=B10)*(Data!$G:$G='St Marg'!$C$1)*(Data!G:G='St Marg'!$C$2)*(Data!G:G='St Marg'!$C$3))

 

[Fluff]

Is the formula on the "St Marg" sheet?

 

[xxsalahxx]

Is the formula on the "St Marg" sheet?

Hi there, Yes formula is on St Marg sheet

 

[Fluff]

Ok, how about

=SUMPRODUCT(--(Data!$B:$B>=A10)*(Data!$B:$B<=B10)*((Data!$G:$G=$C$1)+(Data!G:G=$C$2)+(Data!G:G=$C$3)))

 

[xxsalahxx]

Ok, how about

=SUMPRODUCT(--(Data!$B:$B>=A10)*(Data!$B:$B<=B10)*((Data!$G:$G=$C$1)+(Data!G:G=$C$2)+(Data!G:G=$C$3)))

Thank you so much that worked perfectly!! Headaches gone now!

 

[Fluff]

You're welcome & thanks for the feedback.