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
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" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
sure how can I delete this post so I can report a new one with the excel file?
Do not do that, please. Attach the file to this thread following the instructions I have given above.
ok thank you. I have attached the excel file to this post.
Thank you.
I have tried countifs with conditions but it's not working.
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:
=IF(COUNTIFS($A$2:$A$129,K$1,$B$2:$B$129,$B2,$C$2:$C$129,$C2,$D$2:$D$129,$D2,$E$2:$E$129,$E2,$G$2:$G$129,$G2,$H$2:$H$129,$H2,$I$2:$I$129,$I2),1,"")
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:T2Formula:=IFERROR(AVERAGEIFS(F6:Q6,F$5:Q$5,">="&T$1,F$5:Q$5,"<="&T$2),0)
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.
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
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.
@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
=IF(COUNTIFS($A$2:$A$129,K$1,$B$2:$B$129,$B2,$C$2:$C$129,$C2,$D$2:$D$129,$D2,$E$2:$E$129,$E2,$G$2:$G$129,$G2,$H$2:$H$129,$H2,$I$2:$I$129,$I2),1,"")
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 :/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks