SUM with multiple numeric and text criteria
 

Hello,

I would like to be able to return a variety of metrics for a range of cells
that text and numbers. In the first example, I would like to be able to
return the total number of records where the value €œDev_Assigned€� is in
Column B, the value €œ1-Critical€� is in Column C and the number in Column D is
greater than one but less than 4:


Column A Column B Column C Column D Column E
WUP00020460 Dev_Assigned 1-Critical 2 CW App Issue
WUP00020596 Dev_Assigned 1-Critical 2 CW Env Issue
WUP00020731 Test 1-Critical 1 CW Env Issue
WUP00020465 Dev_Assigned 2-High 2 CW Env Issue
WUP00020461 Test 3-Medium 2 CW Data Issue
WUP00020592 Open 2-High 2 CW User Issue
WUP00020733 Dev_Assigned 1-Critical 4 CW Data Issue
WUP00020464 Test 2-High 2 CW Data Issue
WUP00020465 Dev_Assigned 1-Critical 5 CW App Issue
WUP00020597 Dev_Assigned 1-Critical 3 CW Env Issue
WUP00020738 Test 1-Critical 1 CW Test Issue
WUP00020469 Dev_Assigned 2-High 2 CW Env Issue


In the second example, using the data above, I would like to be able to
return the total number of records where the value €œ1-Critical€� is in Column
C and the value €œCW App Issue€� is in Column E.

Any help or guidance is greatly appreciated!

--
Thanks!
-mcdc01

SUM with multiple numeric and text criteria
 

One way:

=SUMPRODUCT(--(C1:C1000="1-Critical"),--(E1:E1000="CW App Issue"))

for an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

In article ,
mcdc01 wrote:

Hello,

I would like to be able to return a variety of metrics for a range of cells
that text and numbers. In the first example, I would like to be able to
return the total number of records where the value €œDev_Assigned€� is in
Column B, the value €œ1-Critical€� is in Column C and the number in Column D is
greater than one but less than 4:


Column A Column B Column C Column D Column E
WUP00020460 Dev_Assigned 1-Critical 2 CW App Issue
WUP00020596 Dev_Assigned 1-Critical 2 CW Env Issue
WUP00020731 Test 1-Critical 1 CW Env Issue
WUP00020465 Dev_Assigned 2-High 2 CW Env Issue
WUP00020461 Test 3-Medium 2 CW Data Issue
WUP00020592 Open 2-High 2 CW User Issue
WUP00020733 Dev_Assigned 1-Critical 4 CW Data Issue
WUP00020464 Test 2-High 2 CW Data Issue
WUP00020465 Dev_Assigned 1-Critical 5 CW App Issue
WUP00020597 Dev_Assigned 1-Critical 3 CW Env Issue
WUP00020738 Test 1-Critical 1 CW Test Issue
WUP00020469 Dev_Assigned 2-High 2 CW Env Issue


In the second example, using the data above, I would like to be able to
return the total number of records where the value €œ1-Critical€� is in Column
C and the value €œCW App Issue€� is in Column E.

Any help or guidance is greatly appreciated!

SUM with multiple numeric and text criteria
 

That is perfect for the second example, Thank you!

For the first example, would the formula be:

=SUMPRODUCT(--(B1:B1000="Dev_Assigned"),--(C1:C1000="1-Critical"),--(D1:D1000="2,3€�))

Thanks again!

--
Thanks!
-mcdc01


"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(C1:C1000="1-Critical"),--(E1:E1000="CW App Issue"))

for an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

In article ,
mcdc01 wrote:

Hello,

I would like to be able to return a variety of metrics for a range of cells
that text and numbers. In the first example, I would like to be able to
return the total number of records where the value €œDev_Assigned€� is in
Column B, the value €œ1-Critical€� is in Column C and the number in Column D is
greater than one but less than 4:


Column A Column B Column C Column D Column E
WUP00020460 Dev_Assigned 1-Critical 2 CW App Issue
WUP00020596 Dev_Assigned 1-Critical 2 CW Env Issue
WUP00020731 Test 1-Critical 1 CW Env Issue
WUP00020465 Dev_Assigned 2-High 2 CW Env Issue
WUP00020461 Test 3-Medium 2 CW Data Issue
WUP00020592 Open 2-High 2 CW User Issue
WUP00020733 Dev_Assigned 1-Critical 4 CW Data Issue
WUP00020464 Test 2-High 2 CW Data Issue
WUP00020465 Dev_Assigned 1-Critical 5 CW App Issue
WUP00020597 Dev_Assigned 1-Critical 3 CW Env Issue
WUP00020738 Test 1-Critical 1 CW Test Issue
WUP00020469 Dev_Assigned 2-High 2 CW Env Issue


In the second example, using the data above, I would like to be able to
return the total number of records where the value €œ1-Critical€� is in Column
C and the value €œCW App Issue€� is in Column E.

Any help or guidance is greatly appreciated!