Sumproduct, 3 columns and date
 

Hello,

I have the below formula that will find/calculate data from 3 columns. This
gives me overall total days for many months/years. A14 is a part number that
matches F8:F3000 on Overall and shop days is M8:M3000.

=SUMPRODUCT(--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)

Now I want to get more specific and only look for data during a specific
month/year. So using the above formula, if I have a date (C1= 1/2009,
D1=2/2009, etc.) on the current worksheet, I want it to look on Overall for
that specific month/year and give me the total. Date would be column U of
Overall.

What do I need to add to the formula?

Thanks for all the assistance.

Sumproduct, 3 columns and date
 

The below will retrieve the information for the month specified in cell C1

=SUMPRODUCT(
--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),
--(MONTH(Overall!$U$8:$U$3000)=MONTH(c1)),
--(YEAR(Overall!$U$8:$U$3000)=YEAR(c1)),
--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)

'or a shortened version using TEXT()

=SUMPRODUCT(
--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),
--(TEXT(Overall!$U$8:$U$3000,"mmyy")=TEXT(C1,"mmyy") ),
--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)


If this post helps click Yes
---------------
Jacob Skaria


"roy.okinawa" wrote:

Hello,

I have the below formula that will find/calculate data from 3 columns. This
gives me overall total days for many months/years. A14 is a part number that
matches F8:F3000 on Overall and shop days is M8:M3000.

=SUMPRODUCT(--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)

Now I want to get more specific and only look for data during a specific
month/year. So using the above formula, if I have a date (C1= 1/2009,
D1=2/2009, etc.) on the current worksheet, I want it to look on Overall for
that specific month/year and give me the total. Date would be column U of
Overall.

What do I need to add to the formula?

Thanks for all the assistance.

Sumproduct, 3 columns and date
 

Add an array to the formula:

--(TEXT(Overall!$U$8:$U$3000,"myyyy")=TEXT(C1,"myyyy "))

--
Biff
Microsoft Excel MVP


"roy.okinawa" wrote in message
...
Hello,

I have the below formula that will find/calculate data from 3 columns.
This
gives me overall total days for many months/years. A14 is a part number
that
matches F8:F3000 on Overall and shop days is M8:M3000.

=SUMPRODUCT(--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)

Now I want to get more specific and only look for data during a specific
month/year. So using the above formula, if I have a date (C1= 1/2009,
D1=2/2009, etc.) on the current worksheet, I want it to look on Overall
for
that specific month/year and give me the total. Date would be column U of
Overall.

What do I need to add to the formula?

Thanks for all the assistance.

Sumproduct, 3 columns and date
 

That did it.

Thanks.

"Jacob Skaria" wrote:

The below will retrieve the information for the month specified in cell C1

=SUMPRODUCT(
--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),
--(MONTH(Overall!$U$8:$U$3000)=MONTH(c1)),
--(YEAR(Overall!$U$8:$U$3000)=YEAR(c1)),
--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)

'or a shortened version using TEXT()

=SUMPRODUCT(
--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),
--(TEXT(Overall!$U$8:$U$3000,"mmyy")=TEXT(C1,"mmyy") ),
--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)


If this post helps click Yes
---------------
Jacob Skaria


"roy.okinawa" wrote:

Hello,

I have the below formula that will find/calculate data from 3 columns. This
gives me overall total days for many months/years. A14 is a part number that
matches F8:F3000 on Overall and shop days is M8:M3000.

=SUMPRODUCT(--(LEFT(Overall!$C$8:$C$3000,2)="Cl"),--(Overall!$F$8:$F$3000=A14),Overall!$M$8:$M$3000)

Now I want to get more specific and only look for data during a specific
month/year. So using the above formula, if I have a date (C1= 1/2009,
D1=2/2009, etc.) on the current worksheet, I want it to look on Overall for
that specific month/year and give me the total. Date would be column U of
Overall.

What do I need to add to the formula?

Thanks for all the assistance.