+ Reply to Thread
Results 1 to 11 of 11

count # of unpaid months in data, replacing pivot that counted the unpaid months

  1. #1
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    count # of unpaid months in data, replacing pivot that counted the unpaid months

    I am looking to find a formula that will do what this pivot did.

    Esentially the summarized pivot allows you to run a count formula at the end to sount all months, and then the count only returns the months with values. So the =Count for the highlighted columns on the pivot is 6.
    pivot.PNG


    But how do I write that type of formula to the data file so I can get rid of the pivot?

    it would need to count all 12 moths in a year, but only 6 of them have values.

    raw data.PNG

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    sure how can I delete this post so I can report a new one with the excel file?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    Do not do that, please. Attach the file to this thread following the instructions I have given above.

  5. #5
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    ok thank you. I have attached the excel file to this post.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    Thank you.

  7. #7
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    I have tried countifs with conditions but it's not working.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,075

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    Seems to me that what you are attempting to do is necessarily complicated.
    To demonstrate a part of what I believe would be needed a range (K:V) has been populated showing for which months the corresponding sources and lists have values using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The count is in column W
    I feel that you would then need to make a table to get the sums per month showing ones in the first added table and then get the average.
    This is not to mention that you might need a further table that would display the unique sources/lists along with counts and averages so that you aren't having to look through multiple rows of duplicate information.
    I would think a better choice would be to display the pivot table in tabular form, along with the count and average columns as modeled.
    Note: It appeared that originally the columns for average were supposed to only average values for certain months. The column for average that is being modeled in column T has an adjustable range based on the values in T1:T2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    thanks for your response JeteMc. This one is a bit of a brain buster.

    I used a similar countifs function previously and it did not have the same results.. I am really not worried about the averages. The issue I am mainly having is how to I formulate the count of months from the pivot table, using the data set instead.
    Even if you drill down to the below screenshot, it has no data in the columns, but is till summing up a zero.
    So essentially, how do I write a formula that can run a count of months to the raw data, and roll up to (1) for each value with the same conditions, plus return blank for the month with no values, so I can run a 12 month count function and get the same results as pivot?

    count pivot months formula not pivot table.PNG

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,075

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    Perhaps I had a senior moment about getting the sums and thus the average. See if columns Y:AG on the Data sheet are what you are looking for. If so I will edit this post to explain how those columns are populated.

  11. #11
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    Re: count # of unpaid months in data, replacing pivot that counted the unpaid months

    @JeteMC,

    you actually brought up a good point with your excel attachments and the newer formulas. I am wondering if I went the wrong way with my formulas initially.

    here is the breakdown of the excel file attached that is the same thing we have been working on,

    the "sample data" file.
    I am using sumifs to roll up (AK) per formula condition criteria
    then I am adding a count of duplicates (column N) of (column K). This way it will get me the sum of K per each criteria just like the pivot is doing.
    then I am counting the same "x" condition for duplicate values and using the AVERAGEIFS only if value is 'x"

    then I need to countifs based of same condtions sumifs are doing, just like in pivot, run average based of countifs return of months with values.

    Now that I look at all your formulas on your recent file I am wondering if I went in the wrong direction with my formulas. I think I can use your CODE

    Please Login or Register  to view this content.

    to run the count of months with values and then return a 1. Except now I run across the issue where the "x" I used for sumifs may not pick up a 1 when it needs to.

    not sure on this one. Bran buster for sure :/
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-01-2018, 02:03 AM
  2. Identifying UNPAID amounts between Paid documents and Unpaid Documents
    By bearlove05 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-24-2016, 01:06 AM
  3. Replies: 2
    Last Post: 03-19-2014, 07:26 PM
  4. Replies: 8
    Last Post: 09-06-2013, 05:57 AM
  5. unpaid Invoices
    By hasanbirol in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-17-2012, 12:16 PM
  6. How to count unpaid installments.
    By rushatiindia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2008, 01:42 AM
  7. Paid vs Unpaid
    By ems.payroll in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 10-03-2008, 04:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1