SUMPRODUCT where a condition is date falls in month prior to current month

[Aimee S.]

Special thanks to T. Valko for helping me with my prior thread.

I need to take his advice a step further.

This is the formula he provided (updated to meet my specific needs):

=SUMPRODUCT(--('3-FactoryCalculation'!$L$3:'3-FactoryCalculation'!$L$5000=1),--('3-FactoryCalculation'!$C$3:'3-FactoryCalculation'!$C$5000="Base"),--(TEXT('3-FactoryCalculation'!$R$3:'3-FactoryCalculation'!$R$5000,"mmmyyyy")=TEXT(NOW(),"mmmyyyy")))

It only counts projects on Sheet 'FactoryCalculation' if:
Column L has value of 1
Column C has value "Base"
Column R has a date which falls within the present calendar month, whatever month that may be.



Is it possible to use this sort of formula to only count projects in that same sheet where:
Column L has value of 1
Column C has value of "Base"
and Column R has a date which falls within the PREVIOUS month, i.e. the month that is always just prior to the present calendar month


This is required so that every month when we have our monthly Operations Review, we can easily look at all data for the entirey of the previous month.

 

[Aladin Akyurek]

Special thanks to T. Valko for helping me with my prior thread.

I need to take his advice a step further.

This is the formula he provided (updated to meet my specific needs):

=SUMPRODUCT(--('3-FactoryCalculation'!$L$3:'3-FactoryCalculation'!$L$5000=1),--('3-FactoryCalculation'!$C$3:'3-FactoryCalculation'!$C$5000="Base"),--(TEXT('3-FactoryCalculation'!$R$3:'3-FactoryCalculation'!$R$5000,"mmmyyyy")=TEXT(NOW(),"mmmyyyy")))

It only counts projects on Sheet 'FactoryCalculation' if:
Column L has value of 1
Column C has value "Base"
Column R has a date which falls within the present calendar month, whatever month that may be.



Is it possible to use this sort of formula to only count projects in that same sheet where:
Column L has value of 1
Column C has value of "Base"
and Column R has a date which falls within the PREVIOUS month, i.e. the month that is always just prior to the present calendar month


This is required so that every month when we have our monthly Operations Review, we can easily look at all data for the entirey of the previous month.

Try...

A1: 1
B1: Base
C1:

=EOMONTH(TODAY(),-2)+1

D1:

=SUMPRODUCT(
    --('3-FactoryCalculation'!$L$3:$L$5000=A1),
    --('3-FactoryCalculation'!$C$3:$C$5000=B1),
    --('3-FactoryCalculation'!$R$3:$R$5000-DAY('3-FactoryCalculation'!$R$3:$R$5000)+1=C1))

 

[Aimee S.]

That just returns a #VALUE! error

 

[scottylad2]

this type of construct

Excel Workbook
	G	H	I
13	3	base	15/02/2011
14	1	base	02/03/2011
15	3	Bottom	05/02/2011
16	2	Bottom	13/02/2011
17	1	Bottom	17/03/2011
18	1	Top	24/03/2011
19	1	Top	19/02/2011
20	3	Top	11/02/2011
21	1	Bottom	09/03/2011
22	2	Top	12/02/2011
23	1	base	16/03/2011
24	3	base	31/03/2011
25	3	base	17/02/2011
26	3	base	03/04/2011
27	3	Top	18/03/2011
28	3	Top	11/02/2011
29	3	Top	01/04/2011
30
31			2
Pivot Table

 

[Aladin Akyurek]

That just returns a #VALUE! error

Did you enter the criteria as stipulated, in particular the one in C1 which is a formula?

 

[T. Valko]

Special thanks to T. Valko for helping me with my prior thread.

I need to take his advice a step further.

This is the formula he provided (updated to meet my specific needs):

=SUMPRODUCT(--('3-FactoryCalculation'!$L$3:'3-FactoryCalculation'!$L$5000=1),--('3-FactoryCalculation'!$C$3:'3-FactoryCalculation'!$C$5000="Base"),--(TEXT('3-FactoryCalculation'!$R$3:'3-FactoryCalculation'!$R$5000,"mmmyyyy")=TEXT(NOW(),"mmmyyyy")))

It only counts projects on Sheet 'FactoryCalculation' if:
Column L has value of 1
Column C has value "Base"
Column R has a date which falls within the present calendar month, whatever month that may be.



Is it possible to use this sort of formula to only count projects in that same sheet where:
Column L has value of 1
Column C has value of "Base"
and Column R has a date which falls within the PREVIOUS month, i.e. the month that is always just prior to the present calendar month


This is required so that every month when we have our monthly Operations Review, we can easily look at all data for the entirey of the previous month.

For the previous month replace this:

TEXT(NOW(),"mmmyyyy")

With:

TEXT(NOW()-DAY(NOW()),"mmmyyyy")

 

[Aimee S.]

Yes, in C1 I did paste the formula as written

Valko: Your formula amendment worked perfect but Excel caught that there needed to be ))) at the end not just one ).

With the extra 2 parentheses at the end it works perfect (I verfied by auto filtering manually for that same data and received the same count, 29).

Thanks everyone for your collective help on this one! If I were as smart as any of you, I'd be a richer woman hehe

 

[T. Valko]

Valko: Your formula amendment worked perfect but Excel caught that there needed to be ))) at the end not just one ).

With the extra 2 parentheses at the end it works perfect (I verfied by auto filtering manually for that same data and received the same count, 29).

Thanks everyone for your collective help on this one! If I were as smart as any of you, I'd be a richer woman hehe

Those closing ) were needed to complete the entire formula. My suggestion was just a change in a specific portion of the formula.

In any case, glad it worked for you. Thanks for the feedback!

 

[Aimee S.]

Ahhhh. I am too much of a nub to have realized that is what you meant and just pasted the thing over without thinking that part through haha. Live and learn!!!!

 

[Aladin Akyurek]

Make the other set up work too...