Monday, July 24, 2017

yogi_Multi-Criteria Count

Google Spreadsheet   Post  #2212

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-24-2017
question by Real Juan:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/MyDUdN2Lr4A;context-place=forum/docs
Count IF it matches one of multiple strings.
How can I COUNTIF the value is one of different strings. I want to count how many pieces of clothes are yellow or green and a wore by 12 year olds.
Working with one color
=ARRAYFORMULA(countif(D2:G6&C2:C6,B11&B10) )

Doesnt work with two colors.
=ARRAYFORMULA(countif(D2:G6&C2:C6,B11:B12&B11) )




Sunday, July 23, 2017

yogi_Pull Row By Row Latest Account Information From Table Of Data In Another Sheet

Google Spreadsheet   Post  #2211

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-23-2017
question by Guillermo Alda:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/tofb3EnObJs;context-place=forum/docs
query to show only last update
Hi there,

So I have a sales pipeline simple follow up file. 

In the "Input - updates"  sheet I put all the inputs of the accounts (customers) we are trying to sell to. Each row is a sales update.

So in the next sheet, "Output - status", I want to have the exact same columns but in the rows, I only want to see the latest update from each account. If there are duplicates, I only want to see the most recent date update - all the values must be the same that in the row of the last update.

I was thinking of doing it with a Query but it's not working out.

Please help, thanks in advance!

Guillermo


Saturday, July 22, 2017

yogi_Count Cells That Begin With Dates marked as 'dd.mm.yyyy' Of Specified Month And Year (country code Philippines)

Google Spreadsheet   Post  #2210

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-22-2017
question by Emerz:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ccHqOrz-RXQ;context-place=forum/docs

yogi_Count Cells That Begin With Dates Of Specified Month And Year

Google Spreadsheet   Post  #2209
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-22-2017
question by Emerz:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ccHqOrz-RXQ;context-place=forum/docs

Monday, July 17, 2017

yogi_Multi-Criteria Count Based On Entries In Top Row And Left Column And Data In Another Sheet

Google Spreadsheet   Post  #2208
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-17-2017
question by Roy Klossner:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/G3CDV6R0ANc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range

Hi,

I'm trying to do a count of call days for a google calendar for a given set of dates.  Here is the link: https://docs.google.com/spreadsheets/d/17It8HbfmRSHvnYQgSVDYrAcyB43JCslrl5sg61Y9in4/edit?usp=sharing

I have most of it figured out.  I'm using a script to pull the data from the calendar to google sheets then using the COUNT() function to count how many calls a person has within a given date range.  What I would like to do now is count weekends twice.  
For example, Isaac is on call 2/11/17 (Sat) and 2/12/17 (Sun) so I want that to count for 2 in C4.  I REALLY don't want to manually input all the weekend dates into a COUNTIFS () function so was hoping to use a WEEKDAYS() function to do it automatically.  Can someone help me with a formula that will auto-count weekends with a COUNTIFS() string, given a certain person being on call any given Sat or Sunday?

Thanks!
Roy

P.S. Please don't run the script, the calendar link has been removed
---
Yogi,

Gen = number of times a person is on general call, indicated on the calendar by Gen: Name
Gen Sat/Sun = number of times a person is on general call on any given Saturday or Sunday, indicated on the calendar by Gen: Name
Gen (Second) = number of times a person is second on general call, indicated by the parenthesis.  For example Gen: Roy (Isaac), means Roy is on call for general and Isaac is second on call.
Gen (Second) Sat/Sun = number of times a person is second on general call, indicated by the parenthesis on any given Saturday or Sunday.  
OB =  number of times a person is on OB call, indicated on the calendar by OB: Name
OB Sat/Sun = number of times a person is on OB call on any given Saturday or Sunday, indicated on the calendar by OB: Name 
OB (Second) = number of times a person is second on OB call, indicated by the parenthesis.  For example OB: Roy (Isaac), means Roy is on call for OB and Isaac is second on call. 
OB (Second) Sat/Sun = number of times a person is second on OB call, indicated by the parenthesis on any given Saturday or Sunday.  

Thanks,
Roy


Thursday, July 13, 2017

yogi_Compute Row By Number Of Days Persons Worked On Weekends (Saturday and Sunday)

Google Spreadsheet   Post  #2207
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-13-2017
question by Roy Klossner:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/G3CDV6R0ANc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range

Monday, July 10, 2017

yogi_SetUp A Single Chart To Show Dynamically Via A dropDown Data For WeekNumber WeekDay Or AllDays

Google Spreadsheet   Post  #2206
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-10-2017
question by Sam Larimer:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/GNagREZr_Lc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range


I may be blindly searching for a how-to that does not exist, but is it possible to use data validation, formula in charts, dynamic ranges, etc. to create a dynamic chart that measures the same quantities but over different lengths of time?

Document in Question: Chart w/ Dynamic Range

Basically, I'm attempting to create a single chart that measures a float ratio and displays a month's data points by:
  • date in month
  • day name
  • weekly average
However, all this data in one place would be very cluttered and/or useless for comparison. The next best option in my mind is a data-validation to choose which data range is pulled for the chart.


Chart type isn't too important. I have faith in you experts, but let me know if this is a wild goose chase