# Formula help needed

K

#### Karen

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.

T

#### T. Valko

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(D25,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),D25)

Format as [h]:mm

K

#### Karen

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 said:
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(D25,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),D25)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

Karen said:
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.

.

A

#### Ashish Mathur

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

K

#### Karen

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.

R

#### Rick Rothstein

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 said:
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 said:
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(D25,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),D25)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

Karen said:
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.

.

F

#### Fred Smith

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

K

#### Karen

Hi Fred

Thank you so much. It worked.

Many thanks to Valko, Rick and Ashish also...

All of you guys are wonderful.

Regards
Karen