yogi_From Table Of WeekDays And Names Of People Who Made Bookings Count Nuimber Of Bookings Made By Day Of Week

                                           Google Spreadsheet   Post  #1957
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 15, 2015

post by Johanna Hart:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dIFaxU37jHs
Count only if 2 criteria are satisfied

I have a long list of days of the week (A), and a long list of peoples names (B) who made the bookings. I want to produce a table (let's say columns C & D) which will count the number of bookings made on each day of the week. The issue I seem to be encountering is that each of the cells in column B have formula in them, so if I use ISTEXT(B2) it counts the formula as text, even if the box is 'empty.' 

I have been creating a long column out of sight (let's say column E) to match the amount of data in columns A & B, and using a IF 'all of criteria in column B2 cell' (to avoid the ISTEXT issue) then, A2.  And then using my C & D table space to Count each of the days of the week in column E- =COUNTIF(E2:E500,C2).

I am sure that there must be an easier way to create a table (C & D) that counts the number of names in column B for each day of the week..?

Would appreciate your help very much!

A                              B                C                         D                   E

Monday                   Sam            Monday               3                   Monday

Monday                   Ed               Tuesday              1                   Monday

Monday                   Mary           Wednesday         2                   Monday

Monday                                      Thursday             1

Monday                                       Friday                 0

Tuesday                  Tom             Saturday            0                    Tuesday 

Wednesday             Sam             Sunday              0                    Wednesday

Wednesday             Sam                                                             Wednesday 

Wednesday

Thursday

Thursday                 Mary                                                            Thursday

Thursday

-------------------------------------------------------------------------------------------------

yogi_Rank Times That Include Fractions Of Seconds

                                           Google Spreadsheet   Post  #1956
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 14, 2015

post by Thomas Reeve:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OyZoIP6LYiw
Exercise times in sheets

I'm helping a teacher with a spreadsheet that records rock climbing times. He wants to enter something like 3 minutes, 20.4 seconds into a cell and have it display as mm:ss.0. However,  he also wants to rank the times (1st place with fastest time, etc), which means I need this stored as a TIME value. [I have set up a RANK function for him in another cell, but it does not work if the times are text values.]

I have taught him to enter his data as "00:03:20.4" which enters the data as a time value. However, when a time is entered into the cell, the display format always reverts back to "hh:mm:ss" (hiding the tenths of a second). He can "correct" the format by changing the column to the mm:ss.0 format, but this extra step seems to be confusing. 

Is there a way to prevent the cell from reverting the format when I enter a time value?

--------------------------------------------------------------------------------------------------

yogi_Plot For Times With Fractions Of Seconds

                                           Google Spreadsheet   Post  #1955
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 12, 2015

post by Robin Scholten:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/YLJ5ji2MIgM
Add chart for 1500m times

I'm making a spreadsheet with result and on all of my performances I added a chart but for the 1500m it isn't working cause of google spreadsheets not recognizing time's above 1 min.

I changed the cell structure to time with s:mm,00 but it's not working. 

Here's my spreadsheet with results and I would like to have the same type of chart in the 1500 like all my other disciplines.

https://docs.google.com/spreadsheets/d/18DozSNUpRIGjDvgbM7PMSHjnzsyjuuV23XSkraw95Bc/pubhtml 

Hope you guys can help me.

Robin Scholten

------------------------------------------------------------------------------------------------------------------------------

yogi_Compute For DashBoard Number Of Unique Persons Who Visited Designated Cities In Specified Year

                                           Google Spreadsheet   Post  #1954
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 11, 2015

post by angelbojangles:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/qqQl1bvOMiA
How to COUNTIF items in one column only once if there are duplicates in another column?

Sample dummy sheet here

Basically, my goal is to create an interactive graph that works off of countifs from Sheet1. You can see in Sheet1 there are six people (Abby, Bobby, Cindy, Danny, Eddy, and Frankie) who have visited Paris, New York, and London a number of times between 2007-2008.

I'm trying to figure out a countif formula for the interactive graph that counts how many INDIVIDUAL people have visited these cities (in 2007, 2008, or both). So even if Abby visited Paris in 2007 three times, she only gets counted once because she's only one person. 

Does that make sense? 

Thank you!

-----------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row In Column G MilesDriven From Mileage Log Recorded In Column B Of 'Form Responses 1' Sheet

                                           Google Spreadsheet   Post  #1953
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 11, 2015

post by mrludden:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/d3DXmC6EjTQ
Help with performing subraction between 2 adjacent values in the same column

Hi

here is my sheet. In column G of 'Form Responses 1' I am calculating the difference between the values in the same row of column B and the previous row of column B. However, the formula I am using also creates an entry on the next row of column G with a large negative number to deal with the last row. This is probably because I am not as familiar with the OFFSET formula I used to try and deal with the issue. My best solution at this point is to create a query() on a new blank sheet (which I've created but haven't done the query yet) and simply select the columns where column b is not null. Is this the best answer or is there a more efficient formula that will give me the difference between the adjacent rows without having this extra value? You will see I have the same issue in column J as well. Also, is IFERROR(IF()) less efficient than IF(ISBLANK()) ? I usually use the second version and I don't remember why I used the IFERROR instead this time. It seems that the arrayformulas take a little longer to populate on this particular sheet and I had a hunch that this was why so any info there is also much appreciated.

Thanks,

Mike 

-----------------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format A Range Of Cells (vertical or horizontal orientation) To Highlight Top N Values

                                           Google Spreadsheet   Post  #1951
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 03, 2015

yogi_Compute Unit Rate For Specified Quantity Of Items Based On Table Of Multiple Quantity_Unit Rate Table

                                           Google Spreadsheet   Post  #1950
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-28, 2015
question by Kiwi67mum:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/0CAYgbVvC1c
Multiple formulas.

I have 3 columns, each with different amounts.

I enter the quantity in Col B and would like it to identify what the $ amount is that is equal to the quantity. I don't think it is possible but it can't hurt to ask.

          

                                                                                                                Quantity            Rate               1-5 units              6-25 units     Over 25 units

Stencil

Unit

$22.50

$18.50

$15.00

-------------------------------------------------------------------------------------------------

yogi_Compute Bonus For Members Of Teams On Specified Week By Week Basis

                                           Google Spreadsheet   Post  #1949
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-24, 2015
question by Jeff Underhill:

https://productforums.google.com/forum/#!mydiscussions/docs/0X8eG6WRUvY
Countif sum in date range between two sheets

Background: I'm a teacher gamifying my classroom and helping students learn to be prepared. Your help is appreciated by me and my 15 fifth graders!!

Copy of the sheet: https://docs.google.com/spreadsheets/d/1MG5QZfBCos1UzSUE9CQtpHjGNstLxxa53p4JMiRzjpk/edit?usp=sharing

Formula Needed: I'm attempting to count variables p, l, and u (p = prepared; l = late; u = unprepared) in sheet Q4 by weeks on sheet Weekly Bonus XP. I need the formula to be on sheet Weekly Bonus XP in each cell, beginning with cell H2 through V12. I need the new formula to use this formula from sheet Q4

=COUNTIF(C4:C, "p")*5+COUNTIF(C4:C, "e")*5+COUNTIF(C4:C, "l")*3+COUNTIF(C4:C, "u")*1

in an array formula, summing the above variables based on dates. The formula would use the date from sheet Q4 in column A and match it to the range between Monday and Friday on sheet Weekly Bonus XP in rows C through G.

For example, for each p, l, or u David earns in column C between March 31 and April 3 on sheet Q4, the formula on sheet Weekly Bonus XP would sum those values in the week March 31 - April 3 in cell H2.

I know precisely what I'm trying to accomplish and have tried many variations, to no avail. I am studying the syntax, and your help would be very appreciated!!

---

Hi Yogi,

I'm attempting to count the p, l, and u variables in sheet Q4 based on dates and sum them in a cell, per week and student, on sheet Weekly Bonus XP. For each student name in Q4, I need to count up the p, l, and u variables in that student's column IF those variables fall in a date range.

I need a formula on sheet Weekly Bonus XP in cell H2.

I need that formula to look something like =arrayformula(COUNTIF(C4:C, "p")*5+COUNTIF(C4:C, "e")*5+COUNTIF(C4:C, "l")*3+COUNTIF(C4:C, "u")*1)sum(date...))

p = 5 points

e = 5 points

l = 3 points

u = 1 point

So sheet Q4 column C variables will be summed for the week for the student on Sheet Weekly Bonus XP cell H2. That is, IF the date in sheet Q4 column C matches the range of dates in sheet Weekly Bonus XP between columns C2 and G2. The only dates needed in the formula would be C2 and G2, I imagine.

I would like the formula to be reiterative for each student and week.

Does this help? Appreciate your reply!

---------------------------------------------------------------------------------------------------------------------------------------

yogi_LookUp Scores For Specified Names If There Are Matching Scores For Corresponding Names In Sheet1 And Sheet2

                                           Google Spreadsheet   Post  #1948
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-22, 2015
question by Ms. Cleveland:

https://productforums.google.com/forum/#!mydiscussions/docs/xk0vc9YTJyE

Compare columns in different Google Spreadsheets

I have 2 separate spreadsheets that I want to compare. First, I want to compare names in 2 columns in different spreadsheets/tabs. Second, it their is a match, compare the numbers in 2 columns in different spreasheets/tabs that are associated with the name match. Last, if the names and numbers match, place the number next to the name in another column, in the second spreadsheet. The names in the last column are already populated. Here is what I have so far. 

=IF(AND($D$2:$D$100=QBW2!$O$2:$O$100,$E$2:$E$100=QBW2!$P$2:$P$100,E2:E100))

I want to match a name in column D(sheet1) to column O(sheet2).

 I want to match a number in column E(sheet1) to column P(sheet2).

 If the information matches, place the number next to the name(which is in D) in column P(sheet2).

---

Here is the link below.

https://docs.google.com/spreadsheets/d/16SkjPTta11EzpvLqIwGd4MmyMC3Bb6QNSb2Trk7hHG8/edit?usp=sharing

My Objective:

If sheet1 column B matches with sheet2 column C 

AND

If sheet1 column C matches with sheet2 column D

THEN

Place their matching score next to their name in column I

IF NO MATCH OCCURED

Place a "0" in the column next to their name

The names are already populated in row H in order.

------------------------------------------------------------------------------------------------------------------

yogi_Rearrange Original Table Of Item Category Size Into New Table Of Item Size Category And Quantity

                                           Google Spreadsheet   Post  #1947
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by Brian Gorrasi:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/fgZs-JH929A
Count an entry once
Hi All,

I am trying to create a formula for a table but cant figure out how to get it going.  I have a table that has the 3 headers listed below:

Item
Category 
Size

Within the category section are 3 possible terms (High, Mid, Low).

Within the size section are 3 possible terms (Large, Medium, Small).

Within the Item section are multiple terms (and constantly changing)

My goal is to create a second table from this original table that gives me a numeric count of the total items based of their category and size.

Ex.
Original Table
Item      Category     Size
hat            High         L
Hat           high          L
Shoe         High         L
Hat            Mid          M
Shoe         High          S

New Table (from info collected from table 1)

Item           Category      Size        Quantity
Hat               HIgh            L                2
Shoe             HIgh            L                1
Hat               Mid             M                1
Shoe             HIgh            S                1

What formula would I need to write in the Quantity column to give me the proper count per item?
----------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Cells In B5:B If Date In B5:B Is Before Template Update Date In Cell B3

                                           Google Spreadsheet   Post  #1946
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by Speech Scamaticus:

https://productforums.google.com/forum/utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/oGSAqVChq84

Conditional Format based on date less than a calculated date name range

I have in $K$5 cell a calculations for a date 2 1/2 years before the current date which does give me the date I want.
 

=today()-912

On a test spreadsheet, I have created a range for $B$3 to $B$3 called TemplateUpdate as this is the date I want to know if any of my clients last template update is before this date.  FYI, I do biennial website redesigns on all clients and need to know if one is past due to be updated. :)

So I created a conditional for a cell that contains the last date I updated a template for a client.  It format cells with a color if 'Date is before' 'exact date...' 'TemplateUpdate'.  All of the cells in the range B5:9 are formatted with the color yet only 1 of those cells, $B$5 has a date that ie before TemplateUpdate.

Here is a link to the shared spreadsheet if anyone can tell me how to get this so that only cell $B$5 has the 'mauve' background color.

https://docs.google.com/a/webbering.com/spreadsheets/d/1EiTyfMDyH5PnQXUhAuHmLfBQK4nBKWiyI_H0iFol1Go/edit?usp=sharing

Thanks.

Luke

yogi_Conditionally Format Cells Of A Column Based On Cells Of Column Housing Specified Set Of Phrases

                                           Google Spreadsheet   Post  #1945
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by Speech maven:
https://productforums.google.com/forum/utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/_GzEorA7OMM
Multiple exact matches in conditional formatting
I'm trying to use conditional formatting to give a distinct background color to groups of words.  E.g. the words: Horse, Cow, Hog will have a blue background and the words Tower, House, Tent a red background.  Is there a way to enter these as two rules rather than 6? 
----------------------------------------------------------------------------------------------