I have this formula that is totaling all the hours in the G colum if the data
in the C column matches the criteria entered in cell D2.

=SUMIF(Data!$C:$C,$D$2,Data!G:G)
I would like to obtain the above results filtered as follows.
The E column has many 1s, 2s, or 3s in it.
What formula would produce, - the total hours meeting the above criterea
but also only when there is a 1 in the E column ?

Thanks,

Steve

=SUMPRODUCT(--(Data!$C2:$C1000=$D$2),--(Data!$E2:$E1000=1),Data!G2:G1000)

Unless you have XL2007 you cannot specify total columns with SUMPRODUCT.

HTH


"Steve" wrote:

I have this formula that is totaling all the hours in the G colum if the data
in the C column matches the criteria entered in cell D2.

=SUMIF(Data!$C:$C,$D$2,Data!G:G)
I would like to obtain the above results filtered as follows.
The E column has many 1s, 2s, or 3s in it.
What formula would produce, - the total hours meeting the above criterea
but also only when there is a 1 in the E column ?

Thanks,

Steve

=SUMPRODUCT(--(Data!$C$2:$C$100=D2),--(Data!$E$2:$E100=1),Data!$G$2:$G$100)

note that you cannot use the whole column when you use SUMPRODUCT as an
array formula

Thus the C2:C100 etc


--
Regards,

Peo Sjoblom



"Steve" wrote in message
...
I have this formula that is totaling all the hours in the G colum if the
data
in the C column matches the criteria entered in cell D2.

=SUMIF(Data!$C:$C,$D$2,Data!G:G)
I would like to obtain the above results filtered as follows.
The E column has many 1s, 2s, or 3s in it.
What formula would produce, - the total hours meeting the above criterea
but also only when there is a 1 in the E column ?

Thanks,

Steve

Perfect. Worked like a charm.
Thanks to you both.

One last thing - what are the dashes in the formula ?

Tahnks,

Steve

"Toppers" wrote:

=SUMPRODUCT(--(Data!$C2:$C1000=$D$2),--(Data!$E2:$E1000=1),Data!G2:G1000)

Unless you have XL2007 you cannot specify total columns with SUMPRODUCT.

HTH


"Steve" wrote:

I have this formula that is totaling all the hours in the G colum if the data
in the C column matches the criteria entered in cell D2.

=SUMIF(Data!$C:$C,$D$2,Data!G:G)
I would like to obtain the above results filtered as follows.
The E column has many 1s, 2s, or 3s in it.
What formula would produce, - the total hours meeting the above criterea
but also only when there is a 1 in the E column ?

Thanks,

Steve

Hi Steve,
If you really want to know about the two dashes, open Google or some other
search engine and type in "double unary." You should find many hits that will
go through the logic. When I first saw that type of solution, I spent an hour
or so figuring out what the double unary actually did. I know I couldn't
explain it well enough here.
So, get your coffee and put on your thinking cap before proceeding!
--
Ken Hudson


"Steve" wrote:

Perfect. Worked like a charm.
Thanks to you both.

One last thing - what are the dashes in the formula ?

Tahnks,

Steve

"Toppers" wrote:

=SUMPRODUCT(--(Data!$C2:$C1000=$D$2),--(Data!$E2:$E1000=1),Data!G2:G1000)

Unless you have XL2007 you cannot specify total columns with SUMPRODUCT.

HTH


"Steve" wrote:

I have this formula that is totaling all the hours in the G colum if the data
in the C column matches the criteria entered in cell D2.

=SUMIF(Data!$C:$C,$D$2,Data!G:G)
I would like to obtain the above results filtered as follows.
The E column has many 1s, 2s, or 3s in it.
What formula would produce, - the total hours meeting the above criterea
but also only when there is a 1 in the E column ?

Thanks,

Steve

The --(Double Unary) converts a TRUE/FALSE condition to 1/0 which then
permits SUMPRODUCT to do its arithmetic.

Look here for a full explanation of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Steve" wrote:

Perfect. Worked like a charm.
Thanks to you both.

One last thing - what are the dashes in the formula ?

Tahnks,

Steve

"Toppers" wrote:

=SUMPRODUCT(--(Data!$C2:$C1000=$D$2),--(Data!$E2:$E1000=1),Data!G2:G1000)

Unless you have XL2007 you cannot specify total columns with SUMPRODUCT.

HTH


"Steve" wrote:

I have this formula that is totaling all the hours in the G colum if the data
in the C column matches the criteria entered in cell D2.

=SUMIF(Data!$C:$C,$D$2,Data!G:G)
I would like to obtain the above results filtered as follows.
The E column has many 1s, 2s, or 3s in it.
What formula would produce, - the total hours meeting the above criterea
but also only when there is a 1 in the E column ?

Thanks,

Steve