Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT against rows, columns | Excel Worksheet Functions | |||
SUMPRODUCT with Hidden Columns | Excel Worksheet Functions | |||
SUMPRODUCT with Hidden Columns | Excel Worksheet Functions | |||
sumproduct of columns | Excel Worksheet Functions | |||
sumproduct of 2 columns with date and name | Excel Discussion (Misc queries) |