SUMPRODUCT problem

[krispatterson]

Morning all

Data, as follows:

A - list of dates
B - list of names
C&D - list of £ amounts
E - list of figures between 0 & 4
F - list of dates: 01/11/16, 01/12/16, 01/01/17 etc

I need a formula that:
> pulls out all dates from A within a month of the date in F
> sorts by the names in B
> sorts by an array of numbers in E
> adds up the figures from C+D

So, example: I need all instances of dates in November 16 (A), where the name is "Chris" (B), where the figures are 1:3 (E) - then a sum of the £s in C&D on the basis of that criteria.


Again, I'm pretty good at writing singular formulas but when it comes to working out on multiple arrays, I really struggle... I think I need a SUMPRODUCT, but I just can't write it properly!

Can you help please? Thanks in advance!

 

[Special-K99]

Re: SUMPRODUCT problem - basic!

" a sum of the £s in C&D"

Not clear enough.
A single sum ie (C+D) for everything that fits the other criteria?
Or a sum of C and a sum of D for everything that fits the other criteria?

Try

=SUMPRODUCT((MONTH(A1:A1000)=11*(YEAR(A1:A1000)=2016)*(B1:B1000="Chris")*(E1:E1000>=1)*(E1:E1000<=3)*(C1:C1000))
=SUMPRODUCT((MONTH(A1:A1000)=11*(YEAR(A1:A1000)=2016)*(B1:B1000="Chris")*(E1:E1000>=1)*(E1:E1000<=3)*(D1:D1000))

or

=SUMPRODUCT((MONTH(A1:A1000)=11*(YEAR(A1:A1000)=2016)*(B1:B1000="Chris")*(E1:E1000>=1)*(E1:E1000<=3)*((C1:C1000)+(D1:D1000)))

 

[krispatterson]

Re: SUMPRODUCT problem - basic!

" a sum of the £s in C&D"

Not clear enough.
A single sum ie (C+D) for everything that fits the other criteria?
Or a sum of C and a sum of D for everything that fits the other criteria?

Try

=SUMPRODUCT((MONTH(A1:A1000)=11*(YEAR(A1:A1000)=2016)*(B1:B1000="Chris")*(E1:E1000>=1)*(E1:E1000<=3)*(C1:C1000))
=SUMPRODUCT((MONTH(A1:A1000)=11*(YEAR(A1:A1000)=2016)*(B1:B1000="Chris")*(E1:E1000>=1)*(E1:E1000<=3)*(D1:D1000))

or

=SUMPRODUCT((MONTH(A1:A1000)=11*(YEAR(A1:A1000)=2016)*(B1:B1000="Chris")*(E1:E1000>=1)*(E1:E1000<=3)*((C1:C1000)+(D1:D1000)))

Sorry Special-K99, to clarify... the latter. of your 2 points. so, if all other criteria is met, I need a sum of all C+D matches. to simplify, ignore that there are 2 columns and just advise on 1 - I'll then expand for both.

I tried both of the above and struggled. the top 2 both returned '0' values, and the bottom one returned #N/A. Tried to work through the n/a problem step by step but couldn't find the error...

 

[Special-K99]

Re: SUMPRODUCT problem - basic!

Can yopu post the sheet?
create a mockup example if necessary.
You cant attach files on this forum.
upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[krispatterson]

Re: SUMPRODUCT problem - basic!

Can yopu post the sheet?
create a mockup example if necessary.
You cant attach files on this forum.
upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

Hi Special-K99 - sorry for the delayed reply...

Now uploaded on Dropbox - RAW DATA.xlsx


let me know what you think when you get chance please... Thanks

 

[Special-K99]

Re: SUMPRODUCT problem - basic!

Your original request example specified a name of Chris and implied value of E from 1 to 3

There is no "Chris" in column B
There's no point providing an example that does not fit your data.

This works (tested)

in I1
=SUMPRODUCT((MONTH(A$2:A$2000)=MONTH(H1))*(YEAR(A$2:A$2000)=YEAR(H1))*(B$2:B$2000="XXXXX")*(E$2:E$2000>=0)*(E$2:E$2000<=4)*(C$2:C$2000))

Replace xxxxx with the name you want to search for
Adjust the values against the E columns above for whatever range you want to check for.

 

[krispatterson]

Re: SUMPRODUCT problem - basic!

Your original request example specified a name of Chris and implied value of E from 1 to 3

There is no "Chris" in column B
There's no point providing an example that does not fit your data.

This works (tested)

in I1
=SUMPRODUCT((MONTH(A$2:A$2000)=MONTH(H1))*(YEAR(A$2:A$2000)=YEAR(H1))*(B$2:B$2000="XXXXX")*(E$2:E$2000>=0)*(E$2:E$2000<=4)*(C$2:C$2000))

Replace xxxxx with the name you want to search for
Adjust the values against the E columns above for whatever range you want to check for.

Thanks Special-K99, all works perfectly.