• 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

correct formulas to use to help extract data based on specific information |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / correct formulas to use to help extract data based on specific information |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…correct formulas to use to help ext…
sp_PrintTopic sp_TopicIcon
correct formulas to use to help extract data based on specific information
Avatar
Melissa Pine

New Member
Members
Level 0
Forum Posts: 1
Member Since:
November 8, 2018
sp_UserOfflineSmall Offline
1
November 8, 2018 - 10:24 am
sp_Permalink sp_Print

I need help with finding the correct formulas to use to help extract data based on specific information so I don't have to manually count the information out when I dump the raw data. I am trying to count how many times a specific "work center" that an operator clocks to goes over the planned hours. Then in two separate cells, calculate the sum of the "planned hours" and the "actual hours" for the specific work center the operator went over on.  I have attached an example file of the data I collect and am trying to speed up the process of looking at each work centers performance.

Example data I gather:

I have "Termo" work center that exceeded the planned hours 55 times. This is manually counted in excel and entered into cell "B2".

The total planned hours for "Termo" work center that exceeds planned hours is 186.6 hours. This is manually counted in excel and entered into cell "C2".

The actual hours for "Termo" work center that exceeds planned hours is 272.85 hours. This is manually counted in excel and entered into cell "D2". 

 

The data I am looking at is in the following cell locations:

"Termo" is in cell "G" with other work centers

Planned hours is in cell "J"

Actual hours is in cell "K"

 

I have tried =COUNTIFS(G:G,"termo",J:J,"<"&K:K ) formula with no luck and have searched far and wide to find a solution with no luck.

 

Oh, and I am using Microsoft Office Professional Plus 2013.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
November 8, 2018 - 1:30 pm
sp_Permalink sp_Print

Hi Melissa

In cell B2 enter

=SUM(IF($K$18:$K$722>$J$18:$J$722,IF($G$18:$G$722=A2,1,0),0))

This is an array formula so you will need to press CTRL+SHIFT+ENTER instead of ENTER.

You can then copy it downwards till row 7

Hope this helps

Sunny

Avatar
Paul Wakefield

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
July 26, 2013
sp_UserOfflineSmall Offline
3
November 8, 2018 - 7:32 pm
sp_Permalink sp_Print

An alternative is to create a new data column to calculate any excess hours. I used Column O with the formula "=IF(K18>J18,K18-J18,0)" copied down.

i.e. Are there excess hours? If so, calculate those else enter 0.

B2 could then be: =COUNTIFS($G$18:$G$722,$A2,$O$18:$O$722,">"&0)

i.e. Count the number of instances where the Work center matches the name in Column A and where there are excess hours.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
November 9, 2018 - 11:41 am
sp_Permalink sp_Print sp_EditHistory

If you can add a helper column like what Paul suggested, that would be the best as it avoids all the complications of an ARRAY formula.

My formula would be =IF(K18>J18,1,0) in cell O18 since you only wanted to count the instances.

The formula in cell B2 would then be =COUNTIFS($G$18:$G$722,A2,$O$18:$O$722,1)

Sunny

Avatar
Paul Wakefield

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
July 26, 2013
sp_UserOfflineSmall Offline
5
November 9, 2018 - 6:50 pm
sp_Permalink sp_Print

I like Sunny's elegant solution. As a challenge to myself, I thought I would try to find an even shorter formula for O18 et seq.

How about "=--(K18>J18)"?

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
6
November 10, 2018 - 12:26 am
sp_Permalink sp_Print

If you don't want a helper column, I'd suggest:

B2  =SUMPRODUCT(($J$18:$J$722<$K$18:$K$722)*($G$18:$G$722=$A2))

C2  =IF($B2>0,SUMPRODUCT(($J$18:$J$722<$K$18:$K$722)*($G$18:$G$722=$A2)),0)

and copy that across to D2, then fill down.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
November 10, 2018 - 2:34 am
sp_Permalink sp_Print

=(K18>J18)*1 will also work

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Ngoc Tinh, Riny van Eekelen, Alexandra Radu, Monique Roussouw, michael serna
Guest(s) 9
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:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27248

 

Member Stats:
Guest Posters: 49
Members: 31897
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.