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

[Stretlow]

Good morning.

if someone could help me with a formula I'd'd appreciate it. (i'm OK with some basics but this is beyond me unfortunately)

I have a table the following column headers

E = Amount
N = Date Refunded
Y = WriteOff

what i'm looking for is two formulas.

1) Sum Column E where Column N is in the current month and Column Y is blank.
2) Sum Column E where Column N is in the Previous month and Column Y is blank.

I currently have got this far

=SUMIFS(Chargebacks!E:E,Chargebacks!N:N,"<>",Chargebacks!Y:Y,"=")

obviously the bit in red is where i need to define the months but i'm not sure how


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.

Stret

 

[pgc01]

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

Hi Comfy.

Sorry, you are right, I should have read it more attention.

 

[Stretlow]

Sorry

Just had another thought, if i wanted the same calculation but where the date was yesterday, how would one achieve that?

Stret

 

[Comfy]

Hi Comfy.

Sorry, you are right, I should have read it more attention.

Not to worry. Thanks for the feedback it's always welcome.

 

[pgc01]

Sorry

Just had another thought, if i wanted the same calculation but where the date was yesterday, how would one achieve that?

Stret

Hi Stret

For just yesterday, assuming N1:N100 have excel date values (not datetime values or strings) try:

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

 

[Stretlow]

Hi Stret

For just yesterday, assuming N1:N100 have excel date values (not datetime values or strings) try:

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

Hi Again

Ive changed it slightly on my sheet to

=SUMPRODUCT(--('Accounts Stats'!A1:A500=TODAY()-1),--('Accounts Stats'!B1:B533="Amanda"),'Accounts Stats'!D1:D533)

But its bringing me back the #Value!

What have I done wrong?

Stret

EDIT : Fixed it...

Much obliged mate.. thank you

 

[pgc01]

Great. Cheers!

 

[Stretlow]

Morning.

Still arguing with this sheet, am doing ok but have moved onto counts

column G contains dates
column B contains names
Column L contains a 1 value where there is a record ( it was the only way I could get the yesterdays sumproducts to work.)

anyway ive tried these two

=SUMPRODUCT(--(Investigations!G1:G602=TODAY()-1),--(Investigations!B1:B602="Andy S"),Investigations!F1:F602)
AND THIS
=COUNTIFS(Investigations!G1:G602,TODAY()-1,Investigations!B1:B602,"Andy S",Investigations!L1:L602,"1")

but its returning a 0 value and i cant get my head around it.

Any help?

Cheers

Stret

 

[Stretlow]

Hi Chaps

All your other help as proven invauluble however I require a little more help if thats ok.

I have this

=SUMPRODUCT(--(TEXT(Chargebacks!N1:N20608,"yyyymm")=TEXT(EDATE(TODAY(),-1),"yyyymm")),--(Chargebacks!Y1:Y20608=""),--(Chargebacks!X1:X20608=FALSE),Chargebacks!E1:E20608)

to calculate some figures based on last month only.

What i'm looking for is to change the first part so it calculates only the current year.

Is this possible?

Thanks

Stret

 

[Stretlow]

Can anyone please help me with this

Thanks in advance

Stret

 

[pgc01]

Hi

Try:

=SUMPRODUCT(--(YEAR(Chargebacks!N1:N20608)=YEAR(TODAY())),--(Chargebacks!Y1:Y20608=""),--(Chargebacks!X1:X20608=FALSE),Chargebacks!E1:E20608)