Skip navigation

sumif or sumifs

472 Views 13 Replies Latest reply: Feb 11, 2014 4:14 AM by Singh RSS
Singh  Level 1 Level 1 (0 points)
Currently Being Moderated
Feb 7, 2014 6:54 AM

 

 

 

 

 

 

 

 

 

US$ 1,00,000.00

Cash

Groceries

Home

US$ 5,000.00

US$ 95,000.00

Cash

Gasoline

Auto

US$ 360.00

US$ 94,640.00

Cash

Cinema

Entertainment

US$ 375.00

US$ 94,265.00

Cash

Flowers

Gifts

US$ 180.00

US$ 94,085.00

Credit Card

Internet

Mob and Net

US$ 1,500.00

US$ 92,585.00

Credit Card

Clothes

Mob and Net

US$ 500.00

US$ 92,085.00

Credit Card

Movie Rentals

Mob and Net

US$ 100.00

US$ 91,985.00

Debit Card 

Pro Shop

Golf

US$ 200.00

US$ 91,785.00

Debit Card

Restaurant

Food

US$ 400.00

US$ 91,385.00

Cheque

Groceries

Home

US$ 750.00

US$ 90,635.00

Cheque

Subsciption

Home

US$ 1,500.00

US$ 89,135.00

Cash

Equipment

Golf

US$ 120.00

US$ 89,015.00

 

 

 

 

Income and Expenses

January

 

 

 

Cash Expenses

 

 

 

 

Credit Card Expenses

 

 

 

 

Cheque Expenses

 

 

 

 

Debit Card  expenses

 

 

 

 

Savings

 

 

 

 

In the cells below (marked red ) which formula adds up data from the first column(A)  and the fourth (D) ie to say Cash Expenses cell is to show = sumif Column A is Cash then add up all cash expenditure in Column D, and display?

Regards

Singh

(PS the auto format in new Numbers 13 just does not accept columns designated and jumps to its own Rows / Columns)

iMac, OS X Mountain Lion (10.8.3)
  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Feb 7, 2014 7:13 AM (in response to Singh)

    Singh,

     

    This will work best if you remove the summary block and place it in a separate table. Then you can use SUMIF to designate Column A of the entries table as the search range, and Column D of the entries table as the sum range, without having to worry about where the end of the data range is.

     

    Jerry

  • Wayne Contello Level 6 Level 6 (12,660 points)
    Currently Being Moderated
    Feb 7, 2014 7:19 AM (in response to Singh)

    Screen Shot 2014-02-07 at 9.17.01 AM.png

    B17=SUMIF(A$3:A$14,A17, D$3:D$14)

    select B17 and fill down

     

    You should also make the following adjustments:

    1)  edit A17 thru A20 as shown (remove the text "Expense")

    2)  remove the text "US $" from all cells

    3) format columns D and E as currency

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Feb 7, 2014 7:28 AM (in response to Wayne Contello)

    Wayne,

     

    Where Singh is from, US$ may be a valid Currency format prefix. I'm guessing, but it's a possibility.

     

    Jerry

  • Wayne Contello Level 6 Level 6 (12,660 points)
    Currently Being Moderated
    Feb 7, 2014 8:14 AM (in response to Jerrold Green1)

    Jerry, Yes.  That is why I added step (3).  Since I copied the data from the OP post the US $ was simply text. 

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Feb 7, 2014 2:53 PM (in response to Wayne Contello)

    HI Wayne,

     

    "Since I copied the data from the OP post the US $ was simply text."

     

    Copied from the post, it would be text, as I doubt the webpage carries the formatting information specifying currency.

     

    Unless Singh did the calculations in column E manually, though, I suspect the amounts in column D (and E) are numbers, formatted as currency, though. It's possibly a custom format, though. When I tried a variety of currencies from the list provided in the Inspector, none included a space between the currency symbol and the number.

     

    Pasted into this message (below), then copied from here and pasted back into a copy of the table whence they came, only three were recognized as currency; the rest gave the error message shown in the screen shot.

     

    Pasted values to copy:

     

    Cash

    $123.00

    HK$123.00

    AU$123.00

    NT$123.00

    US$123.00

    €123.00

    CHF123.00

    MX$123.00

    ฿123.00

     

    Screen shot of result described above:

    Screen Shot 2014-02-07 at 2.47.31 PM.png

    Note: Column A had been formatted to show Currency. The fact that some of these values had been interpreted as Text was initially masked, as the cells had adopted the alight right setting copied from the pasted table in Discussions. The screen shot shows the appearance after the cell alignment had been set to Automatic.

     

    Regards,

    Barry

  • Wayne Contello Level 6 Level 6 (12,660 points)
    Currently Being Moderated
    Feb 8, 2014 10:28 AM (in response to Singh)

    Singh,

     

    you cannot mix currencies.  You must convert them all to the same currency

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Feb 8, 2014 10:35 AM (in response to Singh)

    Hi Singh,

     

    Here's a restatement of what Jerry and Wayne have provided above, using the same formula as it would appear in the case where the summary table is separate from the data table.

     

    One advantage to separating the summary table from the data table is that range references can be specified as the whole column. Here's an example, usint a copy of the Data part of your table, and a separate Summary table, modeled after the summary part of your table.

    Screen Shot 2014-02-08 at 10.32.20 AM.png

    The formula in B2 of the Summary table is shown below.

     

    =SUMIF(Data :: $A,A2,Data :: D)

     

    "Data" in the formula is the name of the large table. Change to match the name of your data table if necessary.

     

    Enter the formula into B2 of the summary table, then fill down to the 'Cheque' row.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Feb 8, 2014 10:39 AM (in response to Wayne Contello)

    HI Wayne,

     

    "you cannot mix currencies.  You must convert them all to the same currency"

     

    I think you may be looking at my example above, an aside observation on Numbers representation of US$ in 'foreign' localizations.

     

    Barry

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Feb 8, 2014 10:46 AM (in response to Singh)

    Singh wrote:

     

    Hi Jerrold.

    Yup detaching the summary block helped, and is working albeit with a little hickup ie only the first entries of cash, cheque or card are being added, Should figure the correct formula.

    Thanks for the inputs.

    Regards

    Singh

    Now you are on the path to the Numbers way of designing spreadsheets. Keep the different tasks separate. One table for data entry, and another for summarizing.

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.