Excel help

marine1 said:
You are a fken JEDI !!!
Click to expand...

lol. Actually it is a little more complex than I thought.

I am actually using the CountIF function to get the no of rows if it is repeated.
=COUNTIF($A$2:$A$13,A2)
But now my brain has slowed down a bit. I think I am doing this the complicated way. I am sure there is an easier way.

Who Amount Date Count Rows
Peter 100 2018/08/02 6
Peter 100 2018/08/03 6
Mark 100 2018/08/04 4
Mark 100 2018/08/05 4
Peter 100 2018/08/06 6
Peter 100 2018/08/07 6
Sue 100 2018/08/08 2
Peter 100 2018/08/09 6
Peter 100 2018/08/10 6
Mark 100 2018/08/11 4
Mark 100 2018/08/12 4
Sue 100 2018/08/13 2
Click to expand...

I am really hoping we can just ignore all those with more than 5, and not include 5 of the rows
 
Last edited:
[MENTION=64118]Dave[/MENTION] stop it! :crylaugh: You are derailing this very interesting thread. I am waiting in anticipation for the answer.

And I am a something. That clears everything up.
 
KT-B said:
Do you want it to ignore all those items that have more than 5 rows or only include 5 of the rows?

I guess you would create a column before you pivot and use the if function. But what you put in it depends on your answer to the above.
I will see if there is another way to do it.
Click to expand...

i also though of an if formula ..but you need to sort the rows in order all the time

it must only take or count the 1st 5 rows

after that can be deleted or excluded from the pivot
 
[MENTION=150049]I.am.Sam[/MENTION] it took a while but try this:

Add a column to your data and replace the range in the formula with the one that contains the information that shows that it is repeated. I used Format as Table and that is why [@who] exists. It would just be the relevant cell on the current line.

=IF(COUNTIF($A$2:A2,[@Who])<=5,"Y","N")

Who Amount Date another
Peter 100 2018/08/02 Y
Peter 100 2018/08/03 Y
Mark 100 2018/08/04 Y
Mark 100 2018/08/05 Y
Peter 100 2018/08/06 Y
Peter 100 2018/08/07 Y
Sue 100 2018/08/08 Y
Peter 100 2018/08/09 Y
Peter 100 2018/08/10 N
Mark 100 2018/08/11 Y
Mark 100 2018/08/12 Y
Sue 100 2018/08/13 Y

You create your pivot and just filter for the Ys
 
KT-B said:
[MENTION=150049]I.am.Sam[/MENTION] it took a while but try this:

Add a column to your data and replace the range in the formula with the one that contains the information that shows that it is repeated. I used Format as Table and that is why [@who] exists. It would just be the relevant cell on the current line.

=IF(COUNTIF($A$2:A2,[@Who])<=5,"Y","N")

Who Amount Date another
Peter 100 2018/08/02 Y
Peter 100 2018/08/03 Y
Mark 100 2018/08/04 Y
Mark 100 2018/08/05 Y
Peter 100 2018/08/06 Y
Peter 100 2018/08/07 Y
Sue 100 2018/08/08 Y
Peter 100 2018/08/09 Y
Peter 100 2018/08/10 N
Mark 100 2018/08/11 Y
Mark 100 2018/08/12 Y
Sue 100 2018/08/13 Y

You create your pivot and just filter for the Ys
Click to expand...

you could have also manually excluded the ones > 5 from the pivotfield as well, but this is much tidier
 
I.am.Sam said:
I dont want to start a new thread

so i have a data sheet ...i want to do a pivot but it must only count a maximum of 5 rows

so one column has names, one has the date and bla bla

Some people capture 5 or less and some capture 5 or more of the same name on the sheet

What i need is a formula to say that the pivot should ignore any rows where the same name appears more than 5 times

is there something out there or am i thinking wrong ?
Click to expand...

p.s do you really need a pivot ?

Countifs and/or sumifs should work just as well...
 
KT-B said:
There are several ways:

IF you want the two totals separately to know how many days and how many nights
=COUNTIF(B4:K4,"n") and =COUNTIF(B4:K4,"d")
first is the range that includes what you want, then the criteria.

Just to count all Ns and Ds you can use the CountIFS =COUNTIFS(B4:K4,"n",B4:K4,"d")

You could just use the CountA function too. That counts all cells that contain information.
Click to expand...

Would colour counting also work like this?
So instead of counting words?
 
KT-B said:
[MENTION=64118]Dave[/MENTION] stop it! :crylaugh: You are derailing this very interesting thread. I am waiting in anticipation for the answer.

And I am a something. That clears everything up.
Click to expand...

Yes, but what do you identify as :D
 
marine1 said:
Would colour counting also work like this?
So instead of counting words?
Click to expand...

You cannot select colour in pivots. Well not to my knowledge. You would have to use conditional formatting and that would be much harder to do.
The idea is to try and automate it as much as possible.

Your formula for counting colours would also be more complex - not impossible though.

Most of this would be easier with VBA but that is not what was asked.
[MENTION=150049]I.am.Sam[/MENTION] - to be honest - I am struggling with filtering the pivot without showing the field we just created. Working on a solution.
 
KT-B said:
You cannot select colour in pivots. Well not to my knowledge. You would have to use conditional formatting and that would be much harder to do.
The idea is to try and automate it as much as possible.

Your formula for counting colours would also be more complex - not impossible though.
Click to expand...
I wonder I will try and search how to do colours, thanks
 
maumau said:
Yes, but what do you identify as :D
Click to expand...

That depends on the day and my mood. But we are adding Tribbles, cats and other creatures into the selection now. My gender, however, remains unchanged.

Today I identify as a spinning top
 
Last edited:
marine1 said:
I wonder I will try and search how to do colours, thanks
Click to expand...

If you find a solution - please let me know.

And there are tons of videos on pivots. They are one of the most useful reporting tools. They replace the need for many of the other formulas. But pivots can be messed with by "well meaning employees/employers" - so sometimes a static block of data with formulas on a dashboard like sheet is safest.
 
i actually dont even have the source file to mail you ...i was asked by someone else around it and it has been bugging me every since so will try it out later and let you know
 
I.am.Sam said:
i actually dont even have the source file to mail you ...i was asked by someone else around it and it has been bugging me every since so will try it out later and let you know
Click to expand...

lol it was bugging me all night. But I have another idea. Back soon.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X