Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regarding SUMIF
Hi
I have a issue using SUMIF statement my Data is distributed as below Status Priority Ageing Closed P1 2 resolved P1 1 Closed P2 3 Closed P2 4 The Data is distributed like this in more than 3000 cells. Now i want a data like this priority Total ageing P1 3 P2 7 If the status is closed and resolved only we have count that for Total Ageing if the status is other than both above we should not consider. Can anyone help me to resolve this isuue. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regarding SUMIF
Hello Raj,
You can use SUMPRODUCT Assuming your base data is in columns A, B and C then with your priorities, P1, P2 etc. listed in E2 down use this formula in F2 copied down =SUMPRODUCT((A$2:A$100="Closed")+(A$2:A$100="Resol ved"),--(B$2:B $100=E2),C$2:C$100) adjust ranges as necessary... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regarding SUMIF
Assuming row 1 has the labels "Status", "Priority", "aging", and the status
data in in A2:A4000 And that P1 and P2 for results are in D1 and D2 =SUMPRODUCT((($A2:$A$4000="Closed")+($A$2:$A$4000= "Resolved"))*($B$2:$B$4000=D1)*($C$2:$C$4000)) Happy New Year -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters "Raj" wrote in message ... Hi I have a issue using SUMIF statement my Data is distributed as below Status Priority Ageing Closed P1 2 resolved P1 1 Closed P2 3 Closed P2 4 The Data is distributed like this in more than 3000 cells. Now i want a data like this priority Total ageing P1 3 P2 7 If the status is closed and resolved only we have count that for Total Ageing if the status is other than both above we should not consider. Can anyone help me to resolve this isuue. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif | Excel Worksheet Functions | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |