Assistance with Sumproduct(orsumifs) on multiple columns including specifying months (Current and Previous)

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Dec 18, 2013

• #2

Now this will depend on how your months have been entered in Column N.

If they have been entered as dates then:

- For current month

Code:

=SUMPRODUCT(E:E,(MONTH(N:N)=MONTH(NOW()))*(Y:Y<>""))

-For previous month

Code:

=SUMPRODUCT(E:E,(MONTH(N:N)=MONTH(NOW())-1)*(Y:Y<>""))

Stretlow said:

I really need to to do some self learning on sumproduct as I find it difficult to get my head around however any help is greatly appreciated.

Click to expand...

I'm exactly the same, but this forum is a great place to start.

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Dec 18, 2013

• #3

Hi Stret

Try for 1):

=SUMPRODUCT(--(TEXT(Chargebacks!N1:N100,"yyyymm")=TEXT(TODAY(),"yyyymm")),--(Chargebacks!Y1:Y100=""),Chargebacks!E1:E100)


Remark: it's not efficient to use whole columns in the formula. if you need adapt the ranges.

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Dec 18, 2013

• #4

Hi Comfy

3remarks

- if you test only the month you'll for ex. accept the same month of last year
- Now()-1 means yesterday same time, not last month
- better to avoid whole columns, makes formulas slow

 

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Dec 18, 2013

• #5

pgc01 said:

Hi Comfy

3remarks

- if you test only the month you'll for ex. accept the same month of last year
- Now()-1 means yesterday same time, not last month
- better to avoid whole columns, makes formulas slow

Click to expand...

- if you test only the month you'll for ex. accept the same month of last year - Good point, I didn't think of that.

- Now()-1 means yesterday same time, not last month - the formulas reads Month(Now())-1

- better to avoid whole columns, makes formulas slow - agreed.

 

Upvote 0

S

Stretlow

Board Regular

Joined

Dec 17, 2008

Messages

131

• Dec 18, 2013

• #6

Thanks chaps.

Ill have a go at this later today.

The reason i used the whole columns was because the formulas are being used on a sheet that auto populates its data from a sharepoint list so wanted to make sure it catches all the records however theres probably not going to be more than 10k.

I'll post back how im going on.

Thanks again

 

Upvote 0

S

Stretlow

Board Regular

Joined

Dec 17, 2008

Messages

131

• Dec 18, 2013

• #7

=SUMPRODUCT(--(TEXT(Chargebacks!N1:N100,"yyyymm")=TEXT(TODAY(),"yyyymm")),--(Chargebacks!Y1:Y100=""),Chargebacks!E1:E100)

this works perfect, where do I insert the -1 to get last month?

Stret

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Dec 18, 2013

• #8

Stretlow said:

... where do I insert the -1 to get last month?...

Click to expand...

Replace in the formula

TODAY()

with

EDATE(TODAY(),-1)

 

Upvote 0

S

Stretlow

Board Regular

Joined

Dec 17, 2008

Messages

131

• Dec 18, 2013

• #9

pgc01 said:

Replace in the formula

TODAY()

with

EDATE(TODAY(),-1)

Click to expand...

Thats great stuff, cant thank you enough.

Stret

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Dec 18, 2013

• #10

You're welcome. Thanks for the feedback.

 

Upvote 0