yogi_Compute Dates For This Week (CHAPTER 1) And Following Week (CHAPTER 2) Beginning With Specified Weekday

Google Spreadsheet   Post  #2501

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-31-2018

question by: Shannon
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/bUx3SKhLxVg;context-place=mydiscussions
Using Days of Week with IF Function
I have the table at the link below.  Blue text is manually inputted, everything else is formulas.  So I input "Aug 29".  The Aug 30 cell has the formula "=C4+1".  Column B is set equal to Column C with a different date format.

The "Read To" column is the page number I need to read to in my textbook that day (based on cells that are out of view and working fine).  I don't read on Sundays so I'm trying to get all Sunday rows to say the previous day's page number.  So cell D8 would say 41 to match Saturday.

My "Read To" column is using this formula:

=IF($B6 = "Sun", D5, D5+$I$4)

I4 is the cell containing the number of pages to read each day, so not worried about that. "Sun" doesn't seem to be what Google Sheets perceives to be in cell B8 and I'm not sure what to replace it with. The formula in cell B8 is: =C8 (with different date formatting than C8).

Any ideas on how to get the IF function to recognize a day of the week?

Table: https://docs.google.com/spreadsheets/d/1hsRp_4-3Nq9nDFL3hcUPDAMop853bM5wfQtvimeQSP0/edit?usp=sharing

yogi_Sum Of Numbers In Table Starting From Row 7 with Indefinite Number Of Columns By Specified Entities

Google Spreadsheet   Post  #2500

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-30-2018

question by: HoracGzz
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/LTZOh5c3vKo;context-place=forum/docs
A SUMIFS formula but with multiple sum and criteria ranges
Hello, what I need a single formula that can scan multiple columns and sum all values that fit the criteria. Like a SUMIFS formula but with multiple sum and criteria ranges.

I made the following example so you can see what I mean:

https://docs.google.com/spreadsheets/d/1FEGBbfcpYmdYDZW-1aPsFZFKvqUnCG-262Bbk3twLm4/edit#gid=0

Since new columns will be added to this document constantly, I'd prefer a formula that I don't have to update constantly, something other than this: 

=SUMIFS(A$7:A$30,B$7:B$30,"red")+SUMIFS(C$7:C$30,D$7:D$30,"red")+SUMIFS(E$7:E$30,F$7:F$30,"red")+SUMIFS(G$7:G$30,H$7:H$30,"red")+SUMIFS(I$7:I$30,J$7:J$30,"red")+SUMIFS(K$7:K$30,L$7:L$30,"red")

Any help would be greatly appreciated. Thank you for your time.

yogi_Row By Row Count Of Entries In Columns C2 Through N18 >=1

Google Spreadsheet   Post  #2499

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-30-2018

question by: Krish Moeller
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/UuVxTMn1SXY;context-place=forum/docs
COUNTIF the SUM of a Row is Equal to or Larger than 1?

Hello! I am trying to figure out a formula for this, and I feel like this should be easier than I am making it. Any help/expertise would be greatly appreciated. :-)

In short, I am just trying to COUNTIFS all rows for which the SUM of a portion of the row is equal to or larger than 1. 

This is what I came up with: 

=COUNTIFS('Attendance Report'!A2:A,SUM('Attendance Report'!C:N)>=1)

And this is the sheet I am using: https://docs.google.com/spreadsheets/d/1DSEXf1g0xLYNVd79m_E0uwEx1jR1fnLHJvQHdgH-DFI/edit?usp=sharing

Does anyone know what I might be doing wrong?

Thank you so very much!

Best Regards,

Krisha

yogi_Find The Name Of The Person Associated With A Specified Asset As Of The Latest Date

Google Spreadsheet   Post  #2498

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-27-2018

question by: bkca
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/I0h-4l6Iu00;context-place=forum/docs
QUERY help for Google Sheets - find latest entry based on date, with multiple entries

Hi, I have a fairly straighforward question regarding VLOOKUP / QUERY for Google sheets, I'm new to all this and need some help

I have a Google sheets with several tabs.  From the main tab, I want to be able to query the "Laptops" tab in order to find the latest person assigned to a laptop, based on date.

I can get this to work, if I pre-sort the Laptops tab based on date (descending) and then do a VLOOKUP.  It will pull back the first instance, which is the most recent person.

But if the tab gets re-sorted or shuffled, it can cause errors.

I think I need a QUERY function, but not sure how to do this.  Please see the attached photo for my sample data.  Thank you in advance!

yogi_Create PartNos Based On Multiple Criteria From DropDowns In Cells A2 and B2

Google Spreadsheet   Post  #2497

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-27-2018

question by: Eric Plut
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/h-1Y8d4RhJ4;context-place=forum/docs
I am trying to create a spreadsheet to provide a part number based on multiple criteria

yogi_Way To Use Check Boxes In Column A So That They Are Checked Unless There Is A Date Entry In Corresponding Row Of Column B

Google Spreadsheet   Post  #2496

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-25-2018

question by: David Issac Silerman
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/SgKfNhgUJfY;context-place=forum/docs
Using conditional formatting with checkboxes

yogi_Multi Criteria Count -- Number Of Cars Available For Days As Specified From Data In Another Tab

Google Spreadsheet   Post  #2495

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-24-2018

question by: Richie Mastria
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sDDPYPlCXU4;context-place=forum/docs
How good are you at google sheets?
On summary page. Row BCG (A3). 3 days (B3).

I need a formula to calculate how many over 3 days and under 7 days excluding status "done".

The formula I have now to calulcate how many are over 3 days and under 7 is correct:

=countifs(BCGdays, ">3", BCGdays,"<7")

But unfortunately it counts ever one including ones that are done.

I need it to exclude BCGstatus (in name range) is "DONE".

Please help!

Thank you

Richie

https://docs.google.com/spreadsheets/d/11Vo-Rlu0Q1ZHPv-Ro14sZRC6MTl7R12VbDgnCS3XHQk/edit?usp=sharing

yogi_parse cell data across multiple rows of data and display only the parsed value

Google Spreadsheet   Post  #2494

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-22-2018

question by: runners4wellness
https://productforums.google.com/forum/#!topic/docs/FXH4RSRkC7Y;context-place=mydiscussions
How can I parse cell data across multiple rows of data and display only the parsed values?
I am working on variable replacement functionality and attempting to pluck out the data within braces (ie. {variable data} ). The Green table data is the source of what I'm trying to split and transpose, which I'm able to do for B2 ONLY. For some reason, I'm not able to split out anything beyond B2. I'm guessing that I need an ArrayFormula correctly in my below QUERY formula, but unable to get this to work correctly. 

Does anyone have any insights or able to lend a hand? Thanks, in advance!

The instructions are below, but here is a helper spreadsheet that I created for this thread.

The range for the data under Template is RaceRosterAddlDetails:B1:B

Template

The start time for the relay is {SubEventData!6HOUR RELAY2:StartTime} and the Deferral Fee on March 31st is {SubEventData!6HOUR RELAY2:DeferralFeeBy0331}

The fun run event will be on Friday at {SubEventData!SANTA FUN RUN:StartTime}.

I am expecting the yellow highlighted text above to be displayed below, as well. The formula that I'm using below is attempting to pull in the yellow highlighted text (without the open and closed braces "}". 

=QUERY({TRANSPOSE(SPLIT(RaceRosterAddlDetails!$B$2:$B,"\{(.*)\}"))},"select Col1 where Col1 contains ':'",0)

My expected results would include the highlighted value from the above Template data, but it doesn't currently. The sheet where this is located should not impact the query that I'm trying to write.

Created Content Variables	Content Variable Replacement Value
SubEventData!6HOUR RELAY2:StartTime	12:00 AM
SubEventData!6HOUR RELAY2:DeferralFeeBy0331	$30

SubEventData!SANTA FUN RUN:StartTime (Missing this)

yogi_Need a way to find next non-blank cell up or down a column and use in conditional formulas

Google Spreadsheet   Post  #2493

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-21-2018

question by: 

yogi_Compute Days Elapsed In Column E Since Date Opened In Column I

Google Spreadsheet   Post  #2492

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-18-2018

question by: Richie Mastria
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sDDPYPlCXU4;context-place=forum/docs
How good are you at google sheets?
On summary page. Row BCG (A3). 3 days (B3).

I need a formula to calculate how many over 3 days and under 7 days excluding status "done".

The formula I have now to calulcate how many are over 3 days and under 7 is correct:

=countifs(BCGdays, ">3", BCGdays,"<7")

But unfortunately it counts ever one including ones that are done.

I need it to exclude BCGstatus (in name range) is "DONE".

Please help!

Thank you

Richie

https://docs.google.com/spreadsheets/d/11Vo-Rlu0Q1ZHPv-Ro14sZRC6MTl7R12VbDgnCS3XHQk/edit?usp=sharing

yogi_Pull First Artist Name From Artist String In Column A Before Occurrance Of Any Of The Specified Conjunctions

Google Spreadsheet   Post  #2491

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-18-2018

question by: Nick Fox SC
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/AO9f1CtJ4uk;context-place=mydiscussions
Extracting all words from a cell that occur before one of several given words
Hey everyone!

I'm trying to extract all words from a cell that occur before one of several given words. The use case for this is separating metadata to extract the first artist listed in a bunch of different complex artist strings. Below is my practical example:

Johnny Music 

Johnny Music featuring Timothy Muzak

Johnny Music, Timothy Muzak, Kevin Current

Johnny Music & Timothy Muzak feat. Kevin Current

Johnny Music feat. Kevin Current & Timothy Muzak

Johnny Music remixed by Kevin Current

In all cases, the result I want is 'Johnny Music'. 

I've tried a couple of LEFT, TRIM, and MID commands but they aren't letting me search for multiple different places to stop. For example:

=MID(A1;1;MIN(SEARCH("remixed by";A1&"featuring");SEARCH("remixed by";A1&"featuring"))-1)

=LEFT(A1;SEARCH({"remixed by";"&";"featuring"};A1)-1)

Both of these failed to SEARCH beyond 'remixed by', so they only extracted 'Johnny Music' in the last case, not in the other cases (featuring, etc were not located by the search. I got a '#VALUE!' error). 

Can anyone advise? Is there something obvious I am missing, or is this more complex than I first envisaged?

NOTE: This would be applied across multiple artist strings, so it's not as if all of them would start with 'Johnny Music' - therefore a simple LEFT with value parameters won't suffice.

Cheers everyone!

yogi_Multi Criteria Count -- Number Of Cars Available

Google Spreadsheet   Post  #2490

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-17-2018

question by: Richie Mastria
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sDDPYPlCXU4;context-place=forum/docs
How good are you at google sheets?
On summary page. Row BCG (A3). 3 days (B3).

I need a formula to calculate how many over 3 days and under 7 days excluding status "done".

The formula I have now to calulcate how many are over 3 days and under 7 is correct:

=countifs(BCGdays, ">3", BCGdays,"<7")

But unfortunately it counts ever one including ones that are done.

I need it to exclude BCGstatus (in name range) is "DONE".

Please help!

Thank you

Richie

https://docs.google.com/spreadsheets/d/11Vo-Rlu0Q1ZHPv-Ro14sZRC6MTl7R12VbDgnCS3XHQk/edit?usp=sharing

yogi_Recap Monthly Stats For Month Of June From Data For Weeks Of June

Google Spreadsheet   Post  #2489

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-17-2018

question by: Chris Ream
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/wT2M_iGJXzo;context-place=mydiscussions
Tab Formulas

I am trying to get data to rollover from multiple tabs. Ex from my doc. I want tabs 07/02,07/09,07/16 to have the sum of each cell go to the July recap tab. Please help. It would not let me upload as a google doc. I hope you get the idea. 

2018 Weekly Sales Columbus.xlsx

511 KB

2018 Weekly Sales Columbus.xlsx