|
Formula help needed
I have a chart with 5 columns :
Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. |
Formula help needed
If you're using Excel 2007:
Use cells to hold the criteria... F2 = some month like Jan G2 = some section like 3 H2 = some area like B =SUMIFS(D2:D5,A2:A5,F2,B2:B5,G2,C2:C5,H2) This one will work in any version: =SUMPRODUCT(--(A2:A5=F2),--(B2:B5=G2),--(C2:C5=H2),D2:D5) Format as [h]:mm -- Biff Microsoft Excel MVP "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. |
Formula help needed
Hi Biff
Thank you very much. I am using Excel 2003. Tried the =sumproduct formula but got #value! error. Is there anything I must do with the - -? "T. Valko" wrote: If you're using Excel 2007: Use cells to hold the criteria... F2 = some month like Jan G2 = some section like 3 H2 = some area like B =SUMIFS(D2:D5,A2:A5,F2,B2:B5,G2,C2:C5,H2) This one will work in any version: =SUMPRODUCT(--(A2:A5=F2),--(B2:B5=G2),--(C2:C5=H2),D2:D5) Format as [h]:mm -- Biff Microsoft Excel MVP "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. . |
Formula help needed
Hi,
You may create a pivot table - Drag month and section to the row area, Area to the column area and time to the data area -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. |
Formula help needed
Hi Ashish
A pivot table is not ideal for my case. Someone else gave me a formula, it was supposed to work but in my case it ended up with "#N/A". Can you help me take a look? =SUMPRODUCT(($A$2:$A$4=DATEVALUE("Jan"))*($B$2:$B$ 4=3)*($C$2:$C$4="B")*N5:N11) Thank you. "Ashish Mathur" wrote: Hi, You may create a pivot table - Drag month and section to the row area, Area to the column area and time to the data area -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. |
Formula help needed
Did you type it in or did you copy/paste it in? If you didn't copy/paste it,
then try doing that. The two minus signs (with no space between them) effectively multiply a value by 1... in this particular case, that multiplication forces Excel to convert the TRUE or FALSE value returned from the logical comparison to a numerical equivalent (1 for TRUE and 0 for FALSE) in order to perform the mathematical operation. -- Rick (MVP - Excel) "Karen" wrote in message ... Hi Biff Thank you very much. I am using Excel 2003. Tried the =sumproduct formula but got #value! error. Is there anything I must do with the - -? "T. Valko" wrote: If you're using Excel 2007: Use cells to hold the criteria... F2 = some month like Jan G2 = some section like 3 H2 = some area like B =SUMIFS(D2:D5,A2:A5,F2,B2:B5,G2,C2:C5,H2) This one will work in any version: =SUMPRODUCT(--(A2:A5=F2),--(B2:B5=G2),--(C2:C5=H2),D2:D5) Format as [h]:mm -- Biff Microsoft Excel MVP "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. . |
Formula help needed
Karen,
You need to tell us *exactly* what's in column A. Is it the text "Jan" as was shown in your first post? Or is it a date (formatted as mmm) as shown here? Next, your ranges have to be the same length. A2:A4 is 3 cells, but n5:n11 is 7. They all have to be the same number of cells. If they're text, use: =SUMPRODUCT(($A$2:$A$4="Jan")*($B$2:$B$4=3)*($C$2: $C$4="B")*N5:N7) If they're dates, use: =SUMPRODUCT((Text($A$2:$A$4,"mmm")="Jan")*($B$2:$B $4=3)*($C$2:$C$4="B")*N5:N7) Regards Fred "Karen" wrote in message ... Hi Ashish A pivot table is not ideal for my case. Someone else gave me a formula, it was supposed to work but in my case it ended up with "#N/A". Can you help me take a look? =SUMPRODUCT(($A$2:$A$4=DATEVALUE("Jan"))*($B$2:$B$ 4=3)*($C$2:$C$4="B")*N5:N11) Thank you. "Ashish Mathur" wrote: Hi, You may create a pivot table - Drag month and section to the row area, Area to the column area and time to the data area -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. |
Formula help needed
Hi Fred
Thank you so much. It worked. Many thanks to Valko, Rick and Ashish also... All of you guys are wonderful. Regards Karen "Fred Smith" wrote: Karen, You need to tell us *exactly* what's in column A. Is it the text "Jan" as was shown in your first post? Or is it a date (formatted as mmm) as shown here? Next, your ranges have to be the same length. A2:A4 is 3 cells, but n5:n11 is 7. They all have to be the same number of cells. If they're text, use: =SUMPRODUCT(($A$2:$A$4="Jan")*($B$2:$B$4=3)*($C$2: $C$4="B")*N5:N7) If they're dates, use: =SUMPRODUCT((Text($A$2:$A$4,"mmm")="Jan")*($B$2:$B $4=3)*($C$2:$C$4="B")*N5:N7) Regards Fred "Karen" wrote in message ... Hi Ashish A pivot table is not ideal for my case. Someone else gave me a formula, it was supposed to work but in my case it ended up with "#N/A". Can you help me take a look? =SUMPRODUCT(($A$2:$A$4=DATEVALUE("Jan"))*($B$2:$B$ 4=3)*($C$2:$C$4="B")*N5:N11) Thank you. "Ashish Mathur" wrote: Hi, You may create a pivot table - Drag month and section to the row area, Area to the column area and time to the data area -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karen" wrote in message ... I have a chart with 5 columns : Month | Section | Area | Time Jan | 1 | A | 00:30 Jan | 3 | B | 01:30 Feb | 2 | A | 00:30 Feb | 1 | B | 01:30 Need to find out the total time based on different month, Section, Area. That is, if I want the total time for Jan in Section 3, Area B what formula can I use? Thank you. . |
| All times are GMT +1. The time now is 02:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com