need help with formula

[mePenny]

=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

 

[Jacob Skaria]

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

 

[T. Valko]

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.

 

[Jacob Skaria]

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

 

[mePenny]

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]

Ok, try these:

=SUMPRODUCT(--(H1:H100="P"),--(I1:I100=8101),E1:E100)

=SUMPRODUCT(--(H1:H100="P"),--(I1:I100=8032),E1:E100)