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

here is a sample workbook i hope it help

TIA for all help so far

Pa

Thanks.  Did my formula do what you needed?

Regards

Phil

Yes fantastic until i added the home / away column

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

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

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!

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

Thank  you for your reply

Yes trying to count that every time a player gets a yellow playing against a team at home that it counts it and accumulates it to his last number when he last played against them.

(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

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

 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