Can anyone tell me why the "<>5101" is not working in this formula?
=SUMIFS(Table_CAFR.accdb[Current Year],Table_CAFR.accdb[Fund Group],510,Table_CAFR.accdb[Fund],"<>5101",Table_CAFR.accdb[Object],"741*")
I would appreciate any guidance. Thanks in advance for your assistance.
Kathy
Last edited by Comptka; 03-06-2015 at 01:42 PM. Reason: Attached File
Can you post sample data for me to play around with?
you can try
Please Login or Register to view this content.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
I tried that and it did not work; I also tried single quotes around the 5101 thinking it may have something to do with formatting. I did not see a way to attach a file so I just copied some data in. The columns don't line up.
Last edited by Comptka; 03-06-2015 at 01:44 PM. Reason: Attach File
I went back and attached file to original post.
Thanks,
Kathy
I know I can do a second sumifs that subtracts the total for the 5101, but I really would prefer to have it in one sumifs and want to understand why it does not work.
Please see attached with formula that will not work.
Last edited by Comptka; 03-06-2015 at 01:53 PM. Reason: Attach File With Formula
The example you posted doesn't have a formula anywhere, and the Fund: 5101 is TEXT not a number, so you cannot do math or less than on a text.
Okay so it is because it is math. Is there something you can use in place of <> for text in an excel formula?
Nope.
You can convert the entire column to a number with the TextToColumns feature then your formula should work.
=SUMIFS(F2:F5,B2:B5,"<>5101",D2:D5,"741*")
Row\Col Fund Group Fund Char Code Object a_account_desc Current Year Result 510 5100 7I 741000 INTEREST PAYMENTS 510 4100 7I 741100 INTEREST GENERAL OBLIGATON BDS 510 5100 7I 741100 INTEREST GENERAL OBLIGATON BDS 510 5101 7I 741100 INTEREST GENERAL OBLIGATON BDS
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I changed the format to number and it still does not work???
The result you are getting is the same result I am getting. The answer should not include line 5, fund 5101 ($185,346.56). The result should be $1,361,717.97. I converted the fund to number and am still getting the same result.
To clarify what I am trying to accomplish.
I need the total for the current year for all of Fund Group 510 except fund 5101, where the object starts with 741.
Last edited by Comptka; 03-06-2015 at 02:25 PM.
Then this
=SUMIFS(F2:F5,F2:F5,">0",B2:B5,"<>5101",D2:D5,"741*")
If you convert values in column B to numbers the first formula will give you result $1,361,717.97
Please see attached file
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks