Greetings,
So I've been trying to mull around a bit with sumif and sumifs formulas for my particular need, but much to my chagrin I have been unsuccessful with my attempts. I'm currently using Excel 2003 for a rudimentary database and I'm trying to come up with something quick and dirty that will do a few calculations off the data. Basically what I need to know is if I'm using the right tool, and/or if it is possible to do it in Excel and how.
Off the attached file--> I'm trying to get a formula to add up specific months in specific years of invoice data for specific Task Assignments (TAs). So essentially I'm trying to get a formula for the sum of all "Invoiced Amount" where "Month"= X, Y, or Z and "Year"= AAAA for which "TA" = BB and "Company"= "YYY".
I tried starting off with just a month and year formula with =sumif(F:F,and(C:C=or(1,2,3),E:E=2010))
Obviously this is wrong as my result is always zero.
Is this best for programming or is it possible in Excel? Please note I have no knowledge of VBA.
Thanks!
Assuming that SUMPRODUCT is available in Excel 2003, try this in cell G2:
=SUMPRODUCT(--($C$2:$C$20=1),--($E$2:$E$20=$J$2),--($A$2:$A$20=12),--($B$2:$B$20=$I$2),$F$2:$F$20).
Aaaargh! The attachment function here isn't working for me. My formula references these additional lookup tables in columns I and J:
Unique Companies Unique Years
TRX 2010
XRC 2011
TSI 2009
ISC
STSI
Using the lookup tables means you can easily add companies, months, years, without editing your formulas except to expand the ranges. You can add your own for months and whatever other lookups you need.
Trish in Oz
-------------
A problem well defined is a puzzle half solved
if you work with Excel 2003, why did you post an xlsx file? Using 2007 or later, there is also Sumifs() which is a lot faster than Sumproduct()
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks