Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF WITH MULTIPLE CRITERIA
Can anyone help??
I am trying to get sales data for each month from a worksheet and total it. e.g I have ColumnA=Month, ColumnB=Transaction type, ColumnC= Amount What i am trying to do is sum the values of column C but only if column A= "JANUARY" ...... but ..... i also do not want the total if Column B = "invoice", regardless of it being in Jan i.e. the totals for jan and not invoice. Any ideas on how to expand the criteria cus it seems beyond me!! So far i have: =SUMIF(Worksheet!$A$6:$A$1000,"JANUARY",Worksheet! $C$6:$C$1000) I hope it is possible and thanks for trying |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF WITH MULTIPLE CRITERIA
=SUMPRODUCT(--(Worksheet!$A$6:$A$1000="JANUARY"),--(Worksheet!$B$6:$B$1000<"Invoice"),Worksheet!$C$6 :$C$1000) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "franko" wrote in message ... Can anyone help?? I am trying to get sales data for each month from a worksheet and total it. e.g I have ColumnA=Month, ColumnB=Transaction type, ColumnC= Amount What i am trying to do is sum the values of column C but only if column A= "JANUARY" ...... but ..... i also do not want the total if Column B = "invoice", regardless of it being in Jan i.e. the totals for jan and not invoice. Any ideas on how to expand the criteria cus it seems beyond me!! So far i have: =SUMIF(Worksheet!$A$6:$A$1000,"JANUARY",Worksheet! $C$6:$C$1000) I hope it is possible and thanks for trying |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF WITH MULTIPLE CRITERIA
Try this one
=SUMPRODUCT((A2:A100="JANUARY")*(B2:B100<"invoice "),C2:C100) pls do rate "franko" wrote: Can anyone help?? I am trying to get sales data for each month from a worksheet and total it. e.g I have ColumnA=Month, ColumnB=Transaction type, ColumnC= Amount What i am trying to do is sum the values of column C but only if column A= "JANUARY" ...... but ..... i also do not want the total if Column B = "invoice", regardless of it being in Jan i.e. the totals for jan and not invoice. Any ideas on how to expand the criteria cus it seems beyond me!! So far i have: =SUMIF(Worksheet!$A$6:$A$1000,"JANUARY",Worksheet! $C$6:$C$1000) I hope it is possible and thanks for trying |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF WITH MULTIPLE CRITERIA
Sumproduct..... Thanks guys that works perfectly. "Bob Phillips" wrote: =SUMPRODUCT(--(Worksheet!$A$6:$A$1000="JANUARY"),--(Worksheet!$B$6:$B$1000<"Invoice"),Worksheet!$C$6 :$C$1000) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "franko" wrote in message ... Can anyone help?? I am trying to get sales data for each month from a worksheet and total it. e.g I have ColumnA=Month, ColumnB=Transaction type, ColumnC= Amount What i am trying to do is sum the values of column C but only if column A= "JANUARY" ...... but ..... i also do not want the total if Column B = "invoice", regardless of it being in Jan i.e. the totals for jan and not invoice. Any ideas on how to expand the criteria cus it seems beyond me!! So far i have: =SUMIF(Worksheet!$A$6:$A$1000,"JANUARY",Worksheet! $C$6:$C$1000) I hope it is possible and thanks for trying |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF - multiple criteria in different columns | Excel Worksheet Functions | |||
SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF with multiple criteria | Excel Worksheet Functions | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) |