Prev: Dynamic Filtering...Need Help
Next: Count if past today () - 30 days
From: Steve on 16 Apr 2010 11:10
I need to add hours if 3 critera match.
In the below, if there is a 90 in the G column AND a 10 in the J Column, AND
a 100 in the D column, then add the H column. Being that this criterea is
found only in row 3 and row 6, I need the result to be 3 (H3 +H6).

row D G H J

3 100 90 2 10
4 200 100 3 11
5 300 110 4 12
6 100 90 1 10
7 200 90 5 10
8 300 90 6 13

Thanks,

Steve
From: Squeaky on 16 Apr 2010 11:22
Hi Steve,

Using your template as shown, place in K3:

=IF(AND(G3=90,J3=10,D3=100)=TRUE,H3,"")

Copy this down the length of your information, then do a sum of column K in
a convenient place.

Squeaky



"Steve" wrote:

> I need to add hours if 3 critera match.
> In the below, if there is a 90 in the G column AND a 10 in the J Column, AND
> a 100 in the D column, then add the H column. Being that this criterea is
> found only in row 3 and row 6, I need the result to be 3 (H3 +H6).
>
> row D G H J
>
> 3 100 90 2 10
> 4 200 100 3 11
> 5 300 110 4 12
> 6 100 90 1 10
> 7 200 90 5 10
> 8 300 90 6 13
>
> Thanks,
>
> Steve
From: Ms-Exl-Learner on 16 Apr 2010 11:26
Try this...

=SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100)

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Steve" wrote:

> I need to add hours if 3 critera match.
> In the below, if there is a 90 in the G column AND a 10 in the J Column, AND
> a 100 in the D column, then add the H column. Being that this criterea is
> found only in row 3 and row 6, I need the result to be 3 (H3 +H6).
>
> row D G H J
>
> 3 100 90 2 10
> 4 200 100 3 11
> 5 300 110 4 12
> 6 100 90 1 10
> 7 200 90 5 10
> 8 300 90 6 13
>
> Thanks,
>
> Steve
From: getting old on 16 Apr 2010 11:44
I have just been playing with this very same requirement. The Sumifs function
seems to do the job.

(excel 2007)

"Ms-Exl-Learner" wrote:

> Try this...
>
> =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100)
>
> Remember to Click Yes, if this post helps!
>
> --------------------
> (Ms-Exl-Learner)
> --------------------
>
>
> "Steve" wrote:
>
> > I need to add hours if 3 critera match.
> > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND
> > a 100 in the D column, then add the H column. Being that this criterea is
> > found only in row 3 and row 6, I need the result to be 3 (H3 +H6).
> >
> > row D G H J
> >
> > 3 100 90 2 10
> > 4 200 100 3 11
> > 5 300 110 4 12
> > 6 100 90 1 10
> > 7 200 90 5 10
> > 8 300 90 6 13
> >
> > Thanks,
> >
> > Steve
From: Steve on 16 Apr 2010 12:18
This works great. Thank you.

However, I used a simple example. In reality,the J and G data had 5
variables each, so I was going to create tables such as
row G J
3 90 10
4 90 12
5 90 13
6 90 14
7 90 15
8 100 10
9 100 12
10 100 13
11 100 14
12 100 15, etc.
and use the formula not as specific numbers like 90 or 10, but use the cell
reference like G3 and J3. Where my problem comes is that the D column can be
various numbers up to appx. 200, so I'd like to be able to use in the formula
in place of D3:D100 =100, something that would use whatever is in that D
column. Is that possible ?

Thanks again,

Steve

"Ms-Exl-Learner" wrote:

> Try this...
>
> =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100)
>
> Remember to Click Yes, if this post helps!
>
> --------------------
> (Ms-Exl-Learner)
> --------------------
>
>
> "Steve" wrote:
>
> > I need to add hours if 3 critera match.
> > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND
> > a 100 in the D column, then add the H column. Being that this criterea is
> > found only in row 3 and row 6, I need the result to be 3 (H3 +H6).
> >
> > row D G H J
> >
> > 3 100 90 2 10
> > 4 200 100 3 11
> > 5 300 110 4 12
> > 6 100 90 1 10
> > 7 200 90 5 10
> > 8 300 90 6 13
> >
> > Thanks,
> >
> > Steve
 |  Next  |  Last
Pages: 1 2 3
Prev: Dynamic Filtering...Need Help
Next: Count if past today () - 30 days