Log In
Register
Home
April 23, 2020
Offline
how can i adapt my formula to include 1 more column? my present formula is =SUMPRODUCT(--(B$2:B2&E$2:E2=B2&E2),J$2:J2) How can i include
cell G2 ? this is what i came up with but it dont work just returns a 0 - =SUMPRODUCT(--(B$2:B2&E$2:E2&G2:G2="Yc"=B2&E2&G2),J$2:J2)
B is a a team
E is a player
G is Yellow card
J is always a number 1 (but may sometimes b 0) if he has received a yellow card during the game so when all those criterias match it returns 1 and when it next meet the it adds to the previous one.
example - (Team) Knocknagoshel (Player) James (yellow) 1
so if James plays against Knocknagoshel again and receives a yellow then his 1 + 1 = 2 but if he didnt receive a yellow then his previous stays at 1
first formula above works brill only i need to add g2 i dont need
anything else changed only include G2 in formula
See Answer
October 5, 2010
Offline
Hi Pa,
Without your workbook working this out took a lot longer than it could have. I'm still not sure if this is what you want.
=SUMPRODUCT(--(B$2:B2&E$2:E2=B2&E2)*(G2="YC"),J$2:J2)
This post explains how to use SUMPRODUCT by adding other terms using AND and OR logic.
Please supply your workbook if you need further assistance.
Regards
Phil
Answers Post
April 23, 2020
Offline
here is a sample workbook i hope it help
TIA for all help so far
Pa
October 5, 2010
Offline
Thanks. Did my formula do what you needed?
Regards
Phil
April 23, 2020
Offline
Yes fantastic until i added the home / away column
October 5, 2010
Offline
Hi Pa,
So how do you want the Home and Away columns to affect the calculation?
What exactly are you trying to calculate?
I have a feeling that what you are trying to do would be better served by using a pivot table.
I've added 2 sheets to the attached workbook and copied your data onto the Data sheet. I've made some slight modifications which make it work better with a pivot table and created a table to house this data.
The pivot table is on the Pivot Table sheet. You can see that it is easy to see what players received YC's at what venues.
By adding more players, teams or extra columns (for red cards?) to the table on the data sheet, the pivot table will easily adapt to report these for you too.
Regards
Phil
April 23, 2020
Offline
that is absolutely fantastic but unfortunately its not what i need as the its the formula that i require because the work book example i supplied is just a very small example to what i need i have over 50 sheets in the original workbook and other sheets depend on the result formula im hoping to get, i do appreciate your help and thank you for your time in trying to help
i do have other workbook that this pivot table might be useful in time
April 23, 2020
Offline
is it possibe to add column "N" to this formula =SUMPRODUCT(--(B$2:B2&E$2:E2=B2&E2)*(G2="YC"),J$2:J2) "N" would either be home maybe to something like this =SUMPRODUCT(--(B$2:B2&E$2:E2=B2&E2)*(G2="YC")*(N2="Home"),J$2:J2)
ill be using this again in the next column just changing "home" to "away"
but when i try this it just gives #Value!
October 5, 2010
Offline
Pa,
Without seeing how you are trying to use the formula it's impossible to say why you are getting that result.
I asked how do you want the Home and Away columns to affect the calculation, but you haven't said.
Yes you can add more columns into the formula but without knowing what it is you are trying to do I don't know what to tell you.
Are you trying to count yellow cards for each player and have different counts for home and away?
Please see attached it might be what you are after. But as I've previously said, using a pivot table is a better solution. You said you have 50 sheets of data but maybe going through the pain of reorganising them for a PT will be worth it in the end.
Phil
April 23, 2020
Offline
Thank you for your reply
Yes trying
(A) - James gets a yellow today playing at home against cork that counts as 1
(B) - next game James gets a yellow playing away against Limerick that counts as 1
(C) - next game James gets a yellow at home against cork that counts as 1 but add it to (A) totaling 2
(D) - next game James gets a yellow playing against Limerick at home that counts as 1 (as B & C are 1 at home 1 away dont add together)
(E) - next game James gets a yellow playing against Limerick away that counts as 1 (add this to B totaling 2 )
I hope that the above makes some sort of sense i really don't want pivot table only a formula if possible
thank you again for all the help and time in this matter i really do appreciate it
October 5, 2010
Offline
Hi Pa,
The data laid out in your screenshot isn't the same as the data in the workbook you provided.
The workbook I'm attaching here duplicates what you've done on the latest image you've provided.
Phil
April 23, 2020
Offline
i know i just came up with that very quick example after the example workbook....
it works a treat thank you so very much for your time and patience in this matter i appreciate it so very much thank you
1 Guest(s)
