| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if and sum, 2nd try.
This is what I have:
Col G Col H Col I 15 in v 20 ot v 30 in n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and sum, 2nd try.
=sumproduct((I13:I36 ="v")*(H13:H36 ="in")*G13:G36)
-- Don Guillett Microsoft MVP Excel SalesAid Software "Christy" wrote in message ... This is what I have: Col G Col H Col I 15 in v 20 ot v 30 in n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and sum, 2nd try.
You can do it like this:
=SUMPRODUCT((I13:I36="v")*(H13:H36="in")*(G13:G36) ) Hope this helps. Pete On Sep 18, 10:18*pm, Christy wrote: This is what I have: Col G * * Col H * * Col I 15 * * * * * in * * * * *v 20 * * * * * ot * * * * *v 30 * * * * * in * * * * *n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and sum, 2nd try.
Ok, I am totally confussed. I am trying to do the same thing. I have a
column of dates, and another column of {High, Medium, Low, Critical}. I am trying to use SUMPRODUCT to count the number of rows that have Critical on 9/17/2008. Even when I try this: =SUMPRODUCT(B1:B12="Critical") I get 0, and there should be 4. So no way can I get this to work: =SUMPRODUCT((A1:A12="9/17/2008")*(B1:B12="Critical")) What am I messing up?????? Thanks, JR "Pete_UK" wrote: You can do it like this: =SUMPRODUCT((I13:I36="v")*(H13:H36="in")*(G13:G36) ) Hope this helps. Pete On Sep 18, 10:18 pm, Christy wrote: This is what I have: Col G Col H Col I 15 in v 20 ot v 30 in n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and sum, 2nd try.
Ah I think my problem is the date column. How do I select dates using
SUMPRODUCT? "JR" wrote: Ok, I am totally confussed. I am trying to do the same thing. I have a column of dates, and another column of {High, Medium, Low, Critical}. I am trying to use SUMPRODUCT to count the number of rows that have Critical on 9/17/2008. Even when I try this: =SUMPRODUCT(B1:B12="Critical") I get 0, and there should be 4. So no way can I get this to work: =SUMPRODUCT((A1:A12="9/17/2008")*(B1:B12="Critical")) What am I messing up?????? Thanks, JR "Pete_UK" wrote: You can do it like this: =SUMPRODUCT((I13:I36="v")*(H13:H36="in")*(G13:G36) ) Hope this helps. Pete On Sep 18, 10:18 pm, Christy wrote: This is what I have: Col G Col H Col I 15 in v 20 ot v 30 in n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and sum, 2nd try.
You could put the date of interest in a cell somewhere, eg D1, then
you could do this: =SUMPRODUCT((A1:A12=D1)*(B1:B12="Critical")) Or, if you want to stick with your own approach, you would need to do it like this: =SUMPRODUCT((A1:A12=--"9/17/2008")*(B1:B12="Critical")) The -- converts the text date to a proper date. Another way is: =SUMPRODUCT((A1:A12=DATE(2008,9,17))*(B1:B12="Crit ical")) The drawback with these last two is that the date of interest is coded within the formula, so you would have to amend the formula if you wanted to know the result for a different date, whereas with the first formula you can just change the date in D1. You could also put the other criteria in a different cell in the same way. Hope this helps. Pete On Sep 18, 11:05*pm, JR wrote: Ah I think my problem is the date column. *How do I select dates using SUMPRODUCT? "JR" wrote: Ok, I am totally confussed. *I am trying to do the same thing. *I have a column of dates, and another column of {High, Medium, Low, Critical}. *I am trying to use SUMPRODUCT to count the number of rows that have Critical on 9/17/2008. *Even when I try this: =SUMPRODUCT(B1:B12="Critical") I get 0, and there should be 4. *So no way can I get this to work: =SUMPRODUCT((A1:A12="9/17/2008")*(B1:B12="Critical")) What am I messing up?????? Thanks, JR "Pete_UK" wrote: You can do it like this: =SUMPRODUCT((I13:I36="v")*(H13:H36="in")*(G13:G36) ) Hope this helps. Pete On Sep 18, 10:18 pm, Christy wrote: This is what I have: Col G * * Col H * * Col I 15 * * * * * in * * * * *v 20 * * * * * ot * * * * *v 30 * * * * * in * * * * *n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and sum, 2nd try.
Hi,
You may also try the following: =sum(if((range3="v")*(range2="in"),sum_range)) Please press Ctrl+Shift+Enter after the formula. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Christy" wrote in message ... This is what I have: Col G Col H Col I 15 in v 20 ot v 30 in n This is what I need to do: When I13:I36 = v AND H13:H36 = in, THEN add G13:G36 Thanks for your help Christy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
