Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with formula
=SUMIF(H:H,"P",E:E)
The above formula is being used to track purchase orders. I would like to tweak this formula to include another column where i would also track index numbers. The index numbers are 8101 and 8032 and they are in column I. Could someone help me with just this info or do I need to include more? Thanks a bunch mePenny |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with formula
When you have multiple criteria use SUMPRODUCT()
=SUMPRODUCT((H1:H100="P")*(I1:I100=8101),E1:E100) In general =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10) If you are using Excel 2007 you can use SUMIFS() to acheive the same result =SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2) 'or with cells F1 and F2 holding the criteria =SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2) If this post helps click Yes --------------- Jacob Skaria "mePenny" wrote: =SUMIF(H:H,"P",E:E) The above formula is being used to track purchase orders. I would like to tweak this formula to include another column where i would also track index numbers. The index numbers are 8101 and 8032 and they are in column I. Could someone help me with just this info or do I need to include more? Thanks a bunch mePenny |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with formula
Try this...
=SUMPRODUCT(--(H1:H100="P"),--(ISNUMBER(MATCH(I1:I100,{8101,8032},0))),E1:E100) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mePenny" wrote in message ... =SUMIF(H:H,"P",E:E) The above formula is being used to track purchase orders. I would like to tweak this formula to include another column where i would also track index numbers. The index numbers are 8101 and 8032 and they are in column I. Could someone help me with just this info or do I need to include more? Thanks a bunch mePenny |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with formula
If you mean to include both 8101 and 8032 then try the below
=SUMPRODUCT(--(H1:H100="P"), --ISNUMBER(MATCH(I1:I100,{8101,8032},0)),E1:E100) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: When you have multiple criteria use SUMPRODUCT() =SUMPRODUCT((H1:H100="P")*(I1:I100=8101),E1:E100) In general =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10) If you are using Excel 2007 you can use SUMIFS() to acheive the same result =SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2) 'or with cells F1 and F2 holding the criteria =SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2) If this post helps click Yes --------------- Jacob Skaria "mePenny" wrote: =SUMIF(H:H,"P",E:E) The above formula is being used to track purchase orders. I would like to tweak this formula to include another column where i would also track index numbers. The index numbers are 8101 and 8032 and they are in column I. Could someone help me with just this info or do I need to include more? Thanks a bunch mePenny |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with formula
I would like to seperate the index' to two seperate row's so that i can see
how much each is using. The index's hold my quarterly budget so i need to see each seperatly. Sorry for putting this in late. mePenny "T. Valko" wrote: Try this... =SUMPRODUCT(--(H1:H100="P"),--(ISNUMBER(MATCH(I1:I100,{8101,8032},0))),E1:E100) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mePenny" wrote in message ... =SUMIF(H:H,"P",E:E) The above formula is being used to track purchase orders. I would like to tweak this formula to include another column where i would also track index numbers. The index numbers are 8101 and 8032 and they are in column I. Could someone help me with just this info or do I need to include more? Thanks a bunch mePenny . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with formula
Ok, try these:
=SUMPRODUCT(--(H1:H100="P"),--(I1:I100=8101),E1:E100) =SUMPRODUCT(--(H1:H100="P"),--(I1:I100=8032),E1:E100) -- Biff Microsoft Excel MVP "mePenny" wrote in message ... I would like to seperate the index' to two seperate row's so that i can see how much each is using. The index's hold my quarterly budget so i need to see each seperatly. Sorry for putting this in late. mePenny "T. Valko" wrote: Try this... =SUMPRODUCT(--(H1:H100="P"),--(ISNUMBER(MATCH(I1:I100,{8101,8032},0))),E1:E100) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mePenny" wrote in message ... =SUMIF(H:H,"P",E:E) The above formula is being used to track purchase orders. I would like to tweak this formula to include another column where i would also track index numbers. The index numbers are 8101 and 8032 and they are in column I. Could someone help me with just this info or do I need to include more? Thanks a bunch mePenny . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|