A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

If 3 If's, then sum



 
 
Thread Tools Display Modes
  #11  
Old April 16th, 2010, 09:59 PM posted to microsoft.public.excel.worksheet.functions
Mitch
external usenet poster
 
Posts: 159
Default If 3 If's, then sum

Hi Steve,

Try the formula below:

G J H
J
=IF(AND($B2:$B100=90,$D2:$D100=10),SUMIF($D2:$D100 ,10,$C2:$C100),"")

I anchored your table to A1.

D G H J RESULT
100 90 2 10 8
200 100 3 11
300 110 4 12
100 90 1 10
200 90 5 10
300 90 6 13


"Steve" wrote:

Sorry, I forgot to indicate that I'm using 2003, not 2007.

"Mitch" wrote:

Hi Steve,

If that is the case, you can use SUMIFS function.


D G H J GG JJ
100 90 2 10 90 10
200 100 3 11
300 110 4 12
100 90 1 10
200 90 5 10
300 90 6 13

=SUMIFS($H3:$H100,$G3:$G100,$GG$3,$J3:$J100,$JJ$3)

Place the result somewhere you will not paste over.

Hope this helps. Press Yes if it does.

Thanks,

Mitch


"Steve" wrote:

They are all working as designed, but I'm having difficulty being clear.
The D's are the variables that can be numerous, and can change daily.
I guess what I'm trying to say is that, e.g., if there is 100 in the D
then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G.
However, they may also be 150 in D. And if so, then sum up those hours in
H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D
identified as to what they are.

Thanks for your patience.

"Ms-Exl-Learner" wrote:

Not clear whether this is what you are looking for...

1st Formula:-
This will generate the result when D3100 is not blank and when its
matching other criteria
=SUMPRODUCT((D3100"")*(J3:J100=10)*(G3:G100=90 ),H3:H100)

2nd Formula:-
Or simply you can remove the D3100 criteria from the formula like the below
=SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100)

3rd Formula:-
This will generate the result when D3100 is having Numeric Values and when
its matching other criteria. This will leave the Text entries in D3100
eventhough the other criteria's are matching.
=SUMPRODUCT((ISNUMBER(D3100))*(J3:J100=10)*(G3:G 100=90),H3:H100)

Remember to Click Yes, if this post helps!

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


"Steve" wrote:

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 D3100 =100, something that would use whatever is in that D
column. Is that possible ?

Thanks again,

Steve

"Ms-Exl-Learner" wrote:

Try this...

=SUMPRODUCT((D3100=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

  #12  
Old April 16th, 2010, 10:21 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default If 3 If's, then sum

I really appreciate all your effort, but I probably won't be able to really
check it out until Monday. Hopefully I'll get back to you by then.

Thanks again,

Steve

"Mitch" wrote:

Hi Steve,

Try the formula below:

G J H
J
=IF(AND($B2:$B100=90,$D2:$D100=10),SUMIF($D2:$D100 ,10,$C2:$C100),"")

I anchored your table to A1.

D G H J RESULT
100 90 2 10 8
200 100 3 11
300 110 4 12
100 90 1 10
200 90 5 10
300 90 6 13


"Steve" wrote:

Sorry, I forgot to indicate that I'm using 2003, not 2007.

"Mitch" wrote:

Hi Steve,

If that is the case, you can use SUMIFS function.


D G H J GG JJ
100 90 2 10 90 10
200 100 3 11
300 110 4 12
100 90 1 10
200 90 5 10
300 90 6 13

=SUMIFS($H3:$H100,$G3:$G100,$GG$3,$J3:$J100,$JJ$3)

Place the result somewhere you will not paste over.

Hope this helps. Press Yes if it does.

Thanks,

Mitch


"Steve" wrote:

They are all working as designed, but I'm having difficulty being clear.
The D's are the variables that can be numerous, and can change daily.
I guess what I'm trying to say is that, e.g., if there is 100 in the D
then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G.
However, they may also be 150 in D. And if so, then sum up those hours in
H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D
identified as to what they are.

Thanks for your patience.

"Ms-Exl-Learner" wrote:

Not clear whether this is what you are looking for...

1st Formula:-
This will generate the result when D3100 is not blank and when its
matching other criteria
=SUMPRODUCT((D3100"")*(J3:J100=10)*(G3:G100=90 ),H3:H100)

2nd Formula:-
Or simply you can remove the D3100 criteria from the formula like the below
=SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100)

3rd Formula:-
This will generate the result when D3100 is having Numeric Values and when
its matching other criteria. This will leave the Text entries in D3100
eventhough the other criteria's are matching.
=SUMPRODUCT((ISNUMBER(D3100))*(J3:J100=10)*(G3:G 100=90),H3:H100)

Remember to Click Yes, if this post helps!

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


"Steve" wrote:

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 D3100 =100, something that would use whatever is in that D
column. Is that possible ?

Thanks again,

Steve

"Ms-Exl-Learner" wrote:

Try this...

=SUMPRODUCT((D3100=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

  #13  
Old April 17th, 2010, 02:20 AM posted to microsoft.public.excel.worksheet.functions
MS-Exl-Learner
external usenet poster
 
Posts: 135
Default If 3 If's, then sum

Assumption 1:-
If you want to set the D column Criteria (i.e.) D3100=100 instead of
mentioning the 100 in the formula you can refer it to someother cell like the
below
=SUMPRODUCT((D3100=L2)*(J3:J100=10)*(G3:G100=90) ,H3:H100)
Now you can input the D column criteria in L2 cell and the above formula
will run based on the L2 value.
Change the cell reference L2 in the above formula to your desired cell if
required.

Assumption 2:-
If you want to set the D column criteria with its Maximum Or Minimum value
then try the below formula’s.
=SUMPRODUCT((D3100=MAX(D3100))*(J3:J100=10)*(G 3:G100=90),H3:H100)

=SUMPRODUCT((D3100=MIN(D3100))*(J3:J100=10)*(G 3:G100=90),H3:H100)

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


"Steve" wrote:

They are all working as designed, but I'm having difficulty being clear.
The D's are the variables that can be numerous, and can change daily.
I guess what I'm trying to say is that, e.g., if there is 100 in the D
then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G.
However, they may also be 150 in D. And if so, then sum up those hours in
H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D
identified as to what they are.

Thanks for your patience.

"Ms-Exl-Learner" wrote:

Not clear whether this is what you are looking for...

1st Formula:-
This will generate the result when D3100 is not blank and when its
matching other criteria
=SUMPRODUCT((D3100"")*(J3:J100=10)*(G3:G100=90 ),H3:H100)

2nd Formula:-
Or simply you can remove the D3100 criteria from the formula like the below
=SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100)

3rd Formula:-
This will generate the result when D3100 is having Numeric Values and when
its matching other criteria. This will leave the Text entries in D3100
eventhough the other criteria's are matching.
=SUMPRODUCT((ISNUMBER(D3100))*(J3:J100=10)*(G3:G 100=90),H3:H100)

Remember to Click Yes, if this post helps!

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


"Steve" wrote:

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 D3100 =100, something that would use whatever is in that D
column. Is that possible ?

Thanks again,

Steve

"Ms-Exl-Learner" wrote:

Try this...

=SUMPRODUCT((D3100=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

  #14  
Old April 19th, 2010, 04:51 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default If 3 If's, then sum

I don't see how this is accounting for the A row.

I think the below will explain what I'm trying to do better.
I have 7 different employee identifiers. I also have 7 different Job
location identifiers. The master numbers identifying those identifiers will
be in columns X & Y.
X Y
1 Emp. Identifier List Job Identifier List
2 90 10
3 110 11
4 120 12
5 320 13
6 420 14
7 610 17
8 620 18

All the employees can do various jobs. They also have various different work
locations. The work locations may be very many, but in this scenario, I'm
only showing 11 total, 8 different.

So what I'm trying to show results in the E column as follows. Basically
total hours based on all/each of the other 3 different criterea. I can drag
the formula down as far as necessary, because I could envision potentially
quite a few results based on the various locations, employees, and locations.

Here's a few samples.
At location 158, emp 110 worked 2 hrs at job 14
At location 181, emp 110 worked 3 hrs at job 14
At location 210, emp 120 worked a total of 5 hrs at job 17
At location 210, emp 420 worked of 7 hrs at job 17
At location 391, emp 90 worked a total of 13 hrs at job 10, etc.

I also don't need the above wording, if I could just get the numbers like
391 90 10 13 that would be great.

row A B C D
1 Location Emp. Iden Hours Job identifier
2 158 110 2 14
3 181 110 3 14
4 210 120 4 17
5 210 120 1 17
6 210 420 7 17
7 296 610 6 11
8 310 620 3 17
9 311 90 2 10
10 333 110 2 11
11 391 90 8 10
12 391 90 5 10

Again, thank you very much for your patience, and if you want to bail out
afterall my poor explaining, I wouldn't blame you.

Thanks again,

Steve

"Mitch" wrote:

Hi Steve,

Try the formula below:

G J H
J
=IF(AND($B2:$B100=90,$D2:$D100=10),SUMIF($D2:$D100 ,10,$C2:$C100),"")

I anchored your table to A1.

D G H J RESULT
100 90 2 10 8
200 100 3 11
300 110 4 12
100 90 1 10
200 90 5 10
300 90 6 13


"Steve" wrote:

Sorry, I forgot to indicate that I'm using 2003, not 2007.

"Mitch" wrote:

Hi Steve,

If that is the case, you can use SUMIFS function.


D G H J GG JJ
100 90 2 10 90 10
200 100 3 11
300 110 4 12
100 90 1 10
200 90 5 10
300 90 6 13

=SUMIFS($H3:$H100,$G3:$G100,$GG$3,$J3:$J100,$JJ$3)

Place the result somewhere you will not paste over.

Hope this helps. Press Yes if it does.

Thanks,

Mitch


"Steve" wrote:

They are all working as designed, but I'm having difficulty being clear.
The D's are the variables that can be numerous, and can change daily.
I guess what I'm trying to say is that, e.g., if there is 100 in the D
then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G.
However, they may also be 150 in D. And if so, then sum up those hours in
H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D
identified as to what they are.

Thanks for your patience.

"Ms-Exl-Learner" wrote:

Not clear whether this is what you are looking for...

1st Formula:-
This will generate the result when D3100 is not blank and when its
matching other criteria
=SUMPRODUCT((D3100"")*(J3:J100=10)*(G3:G100=90 ),H3:H100)

2nd Formula:-
Or simply you can remove the D3100 criteria from the formula like the below
=SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100)

3rd Formula:-
This will generate the result when D3100 is having Numeric Values and when
its matching other criteria. This will leave the Text entries in D3100
eventhough the other criteria's are matching.
=SUMPRODUCT((ISNUMBER(D3100))*(J3:J100=10)*(G3:G 100=90),H3:H100)

Remember to Click Yes, if this post helps!

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


"Steve" wrote:

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 D3100 =100, something that would use whatever is in that D
column. Is that possible ?

Thanks again,

Steve

"Ms-Exl-Learner" wrote:

Try this...

=SUMPRODUCT((D3100=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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.