• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Adding new criteria to old criteria in SUMPRODUCT|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Adding new criteria to old criteria in SUMPRODUCT|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Adding new criteria to old criteria…
sp_PrintTopic sp_TopicIcon
Adding new criteria to old criteria in SUMPRODUCT
Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
1
April 23, 2020 - 4:03 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
April 23, 2020 - 11:43 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
3
April 23, 2020 - 11:21 pm
sp_Permalink sp_Print

here is a sample workbook i hope it help

 

TIA for all help so far

 

Pa

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
April 23, 2020 - 11:40 pm
sp_Permalink sp_Print

Thanks.  Did my formula do what you needed?

Regards

Phil

Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
5
April 24, 2020 - 2:27 am
sp_Permalink sp_Print

Yes fantastic until i added the home / away column

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
April 24, 2020 - 11:16 am
sp_Permalink sp_Print

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

Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
7
April 24, 2020 - 5:51 pm
sp_Permalink sp_Print

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

Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
8
April 26, 2020 - 12:56 pm
sp_Permalink sp_Print

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!

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
9
April 26, 2020 - 3:12 pm
sp_Permalink sp_Print

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

Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
10
April 26, 2020 - 7:10 pm
sp_Permalink sp_Print

Thank  you for your reply

Yes tryingexample-1.JPGImage Enlarger

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

sp_PlupAttachments Attachments
  • sp_PlupImage example-1.JPG (58 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
11
April 28, 2020 - 5:54 pm
sp_Permalink sp_Print

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

Avatar
Pa Kirwan
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 23, 2020
sp_UserOfflineSmall Offline
12
April 29, 2020 - 8:31 pm
sp_Permalink sp_Print

 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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, MohamedGadAllah, Jeff Krueger, RAMEZ ATTAR, Kylara Papenfuss
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.