Excel version of formula from Mac Numbers

[cbullock14]

I use Mac's Numbers more often than Excel but have a need to find the Excel version of a Numbers formula. The formula in Numbers is SUMIF(Transactions::$CATEGORY),A2,(Transactions::AMOUNT)

The formula allows me to summarize details (dollar amounts) in one table into another table that lists all the Categories & summarizes all of the dollar amounts listed in the detailed table. Here is a screen shot of the Numbers spreadsheet showing the formula:
Numbers Shot.jpg

[TMS]

Try:

Please Login or Register  to view this content.

Where CATEGORY and AMOUNT both need to be Named Ranges.


Regards, TMS

[cbullock14]

Thanks but I am missing something. The error message I get is:

Excel Shot.jpg

It is looking for the Transaction info when it is in the spreadsheet. Thank you of your help; off line for a while but I will keep my ears to the tracks!

[TMS]

I was assuming that Transactions was the sheet name. If not, change it. In fact, if you have defined Named Ranges, you probably don't need it anyway.

Regards, TMS

[cbullock14]

I think I figured this out myself by exporting the Numbers spreadsheet into Excel then looking for the “translated” formula. The formula appeared as:

=SUMIF(‘Checking April 2014 - Transacti'!$G2:$G35,A8,'Checking April 2014 - Transacti’!F2:F35)

I tried this but it kept looking for Checking April 2014. I renamed the Excel tab to Trans so it would fit (maybe eliminated any errors) and removed the Checking April 2014 from the formula so it looked like this:

=SUMIF(Trans!$G2:$G35,J4,Trans!F2:F35)

IT WORKED! I could not cut & paste the formula the way I can in Numbers since J4 kept still but the G2:G35 & F2:F35 kept moving down the spreadsheet so the last few Categories were missed. I manually fixed them so issue appears SOLVED!

[TMS]

Glad you have a solution.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[cbullock14]

I will but I am still trying to figure out how I can have the right formula in one cell, then copy that formula into the next cell and have some of the criteria change to match that cell.

[TMS]

Dollar ($) signs make the cell reference absolute, relative in part, or totally relative. $G$2 is absolute and will not auto adjust. $G2 has an absolute column reference but the row will auto-adjust as the formula is dragged down the sheet. G$2 has an absolute row reference but the column will auto-adjust as the the formula is dragged across the sheet. G2 is a relative address and will auto-adjust in both directions.


Regards, TMS

[cbullock14]

Thank you !

[TMS]

You're welcome. Thanks for the rep.

Please mark as solved.