KnutsonBM
asked on
Convert SUMIFS to work in Excel 2003
=SUMIFS(H:H,A:A,J2,D:D,"In itiating User")
I am currently using excel 2007 but I am building some charts for other users, I used a LOT of SUMIFS in this workbook, but unfortunately this won't work on their computers due to having xl2003. I wish everybody would just convert to 2007 but we have some stubborn folks here, any help converting this to work in 2003 would be appreciated
-Brandon
I am currently using excel 2007 but I am building some charts for other users, I used a LOT of SUMIFS in this workbook, but unfortunately this won't work on their computers due to having xl2003. I wish everybody would just convert to 2007 but we have some stubborn folks here, any help converting this to work in 2003 would be appreciated
-Brandon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
haha you guys must be psychic, answering my question before i ask it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
lots of xovers...
In Excel 2003 you cannot use whole rows/columns for what are essentially array operations, which this is (even though it is not set up as an array formula).What you can do is set up dynamic Names.For example, create a Name, ColA, referring to (if the data are numeric or dates):='Sheet Name'!$A$1:INDEX('Sheet Name'!$A:$A,MATCH(10^300,' Sheet Name'!$A:$A))or if text:='Sheet Name'!$A$1:INDEX('Sheet Name'!$A:$A,MATCH("ZZZZZZZ ZZZZZZ",'S heet Name'!$A:$A))Do the same for the other columns. Now your fomula becomes:=SUMPRODUCT((ColA= J2)*(ColD= "Initiatin g User")*(ColH))
>>haha you guys must be psychic, answering my question before i ask itYes, well, when it's a formula question, we have to use black magic to overcome barry's supernatural powers :)
ASKER
doh, i should have given credit to more than one
how do i reopen this..................sorr y guys
how do i reopen this..................sorr
and even then we usually fail...
KnutsonBM,No worries. Just click 'request attention', and in the reason field ask the Mods to reopen the question :)Patrick
Click the 'request attention' link in the original question box - explain what you want - that's all.
Patrick(ab)
Patrick(ab)
ASKER