yogi_Pull The Names And Percentage Of Those Who Were InvitedAndAttended From Lists Of WhoWereInvited And WhoAttended

                                         Google Spreadsheet   Post  #1621
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-30-2014
post by Ravel Fagundes: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/1dKa8DkQ0_A)
How can I compare two lists and get a percentage of results in common

Hi, 

I Would like to know how can I compare two columns and get a result of names in common and percentage of presence. 

Column A (Guest list names) 

John 

Paul 

George 

Ringo 

Column B (List of names that were present) 

John 

Paul 

George 

Column C (List of names in common between the lists) 

John 

Paul 

George 

Column D (Percentage of presence) 

75% 

- 

Sorry for my english.

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

yogi_Count Instances Of Years Listed In Range Consisting Of Specified Starting And Ending Column

                                         Google Spreadsheet   Post  #1620
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Prequel: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/zirdsPTg2tE)
Countif to get multiple results from same range
2010
2011
2010
2011
2012

Think about I want to count all of the years here.

2010 = 2
2011 = 2
2012 = 1

I don't want to combine the result, I want them to be seen seperately, just like above.

Multiple COUNTIFs can do that BUT if I want to add more cells I have to change ALL ranges of all COUNTIFs. I want to change the range just once for all formulas for each time I add more entries and they are using the new range all together. I'm guessing the only way to do that is to have different COUNTIFs but have the range information from somewhere else. I'm not sure if it's possible to have one single formula to get multiple results, listed seperately. Oh, and, I have multiple columns, if that makes any difference. (I couldn't use a single countif formula for counting a single criteria in multiple columns, for example)

Note that the "s" here is for plural, I can't use "countifs", I don't use the new version of Google Docs.

Thanks in advance.
-------------------------------------------------------------------------------------------------------------------------------------
in the following is a solution to a bit more generalized problem

yogi_Add To yogi_signs Items From Inventory That Have Not Already Been Assigned

                                         Google Spreadsheet   Post  #1619
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Jacob W. Kerr: (compare 2 columns of address to creat a list of missing address)
I am making a sheet that keeps track of properties that have real estate signs put out for them.
I have a pull from another sheet that has the master inventory that pulls all properties with a available status.
In this document I have a tab that has validated field of street address that are manually selected, a vlookup attached to that field to pull the info for the sign (rest of address, beds, bath, price)
I want a section at the top that would be a list of address that are in the pull (available properties) but not not already on the page. The user would see what new address need to added in the correct area of the document.

so basically I want to compare two columns, a master list and a smaller list and get a new column that has the items missing from the master list.

-Jake
---
https://docs.google.com/a/spinhouses.com/spreadsheets/d/121GSMPxbi21xe5PC_jaFKHL0Qz3VJKr5m_373B_xE58/edit#gid=0

let me know if you need further explanation.

-Jake
----------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Rider Payouts By Name And Date Or Range Of Dates Based On Data In 'Fees List'

                                         Google Spreadsheet   Post  #1618
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Margot Con: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dIJM6a-TF5E)
Trouble with Arrayformula and sumfilter or sumproduct

Hi,

I'm having trouble finding a sum of data filtered for two different conditions (date, and name)

Sample spreadsheet here: https://docs.google.com/spreadsheets/d/1Hn1g1cVa-fordJFh5_F1Br2bPDPcHrthLVVjDIS8i50/edit?usp=sharing

The "Fees List" sheet contains the bulk of the data that I'm looking at. The "Rider Payouts" is where I'm trying to use the functions. In column B is a SUMIF function that gets the total $ per name for the whole Fees List sheet. I want to also be able to find totals by date, or even/especially by date range. 

I tried both of the following formulas, in columns F and G, to see which I could get to work:

=ArrayFormula(sumproduct(('Fees List'!B$5:B=A3)*('Fees List'!A$5:A=H$1)*'Fees List'!G$5:G))

=ArrayFormula(SUM(IF('Fees List'!B$5:B=A3,IF('Fees List'!A$5:A=G$1, 'Fees List'!G$5:G,0),0)))

Both formulas result in 0 for all rows.

I put a date in G1 as a reference point to try and filter by that date. I'd actually ideally like to be able to use a range of dates, but I wasn't sure how to do that, and wanted to get the rest of the function working before I played around with that.

Any thoughts on what I'm doing wrong? A syntax problem or reference error??

thanks!

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

yogi_Compute Sum Of Quantities By Various Types of Equipment From Merged Data (from a number of sheets)

                                         Google Spreadsheet   Post  #1617
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Michael Lance: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/7LtZTjNHCQs)
How do you combine like information with query and sum?

Here is my spreadsheet:

https://docs.google.com/spreadsheets/d/1Ty-YkaCrWqELslLlcV2WZBrS8zXlc5O4tZBABPLuZ_4/edit?usp=sharing

On the "By Vendor" sheet I am using Query to pull data from several sheets organized by vendor. What I am missing is how to get the formula to add up all of the like gear types and their quantities into a single listing for that gear type with the total of the specific gear type added up. 

Example right now it looks like this...

12 Circuit DogHouse	1
12 Circuit DogHouse	1
12 Circuit DogHouse	1
12 Circuit DogHouse	1
12 Circuit DogHouse	1
12 Circuit DogHouse	2
12 x 12 10'	8
12 x 12 5 Way Corner	5

And I'd like it to compile it like this if possible...

12 Circuit DogHouse	7
12 x 12 10'	8
12 x 12 5 Way Corner	5

For some reason I can't figure out the correct syntax for adding the sum function in...

Maybe there is a better way?

Thanks! -M

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

yogi_Compute Sum Of Quantities Of Products Listed In 'RIEPILOGATIVO' By Each Month And Whole Year From The Transactions Listed In 'MERCER'

                                         Google Spreadsheet   Post  #1616
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by ANTONINO L: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/L3igpL-dKyY)
FILTER O FORMULA FOR PROBLEM

this post supplements the solution presented in my following blog post:
yogi_Compute Sum Of Quantities Of Products Listed In Sheet2 By Month From The Transactions Listed In Sheet1
http://yogi--anand-consulting.blogspot.com/2014/04/yogicompute-sum-of-quantities-of.html
---

Hello, 

I've tried and I can not get it to work. 

Set directly in the spreadsheet, I would be grateful if you could help me. 

https://docs.google.com/spreadsheets/d/1g_L9IlbGJ6d3HbuNB2zbTDhBh5v3x9ncoQeCJkxKqcQ/edit?usp=sharing

thanks

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

I have presented the solution in sheet named yogi_RIEPILOGATIVO

yogi_Compute Sum Of Quantities Of Products Listed In Sheet2 By Month From The Transactions Listed In Sheet1

                                         Google Spreadsheet   Post  #1615
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2014
post by ANTONINO L: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/L3igpL-dKyY)
FILTER O FORMULA FOR PROBLEM

Who can help me to solve this problem: 

I would like to sum ​​the number of packages in a column having a specific code for each month. 

How can I do?

Thanks

SHEET 1				SHEET 2
DATI				SUMMARY
COD	DATA	Q.TA'		COD	DESCR	Q.TA' GEN	Q.TA' FEB	Q.TA' MAR
64	01/03/2014	5		64	PROD1			5
2227	13/02/2014	4		2227	PROD2		10	5
2227	11/03/2014	5		3339	PROD3
3456	09/01/2014	13		3456	PROD4	13
2227	27/02/2014	6

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

yogi_Apply Conditional Formatting (red yellow orange green background) To Columns C F And I For Project Completion Status

                                         Google Spreadsheet   Post  #1614
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2014
post by cory kelly: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ve1m8m3imbQ)

yogi_Apply Conditional Formatting To 'Project Due Date' Column Based On MileStones (flags) In Table C1 to E6

                                         Google Spreadsheet   Post  #1613
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
post by cory kelly: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ve1m8m3imbQ)
Conditional Formating - How To: Change cell color according to date range

I've been searching everywhere and can't find the answer, but I know it's got to be out there.

I have tasks that have a scheduled "complete by" date. I want them to change colors as real time gets closer to that complete date.

If A1 is equal to or less than 7 days away, turn background red (This is the only one I can get working...)

If A1 is greater than 7 days away and less than or equal to 14 days away, turn bg orange.

If A1 is greater than 14 days away and less than or equal to 30 days away, turn bg yellow.

If A1 is greater than 30 days away, turn green.

Any tips would be great! Thanks

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

following is a solution to a bit more generalized problem

yogi_Split A String Into Its Constituent Characters And Sum Up The Numeric Digits Ignoring The Non-Numeric Characters

                                         Google Spreadsheet   Post  #1612
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
post by Gillermo: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/b4zmtDKeIe8)
Divide string to rows?

this is a followup question to the one addressed in the following blog post:

yogi_Split A String Into Its Constituent Characters
http://yogi--anand-consulting.blogspot.com/2014/04/yogisplit-string-into-its-constituent.html

Follow up:
What about the SUM array thing for this method? For digits that is. Just sum them up and not print the parts one per row.
-------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Split A String Into Its Constituent Characters

                                         Google Spreadsheet   Post  #1611
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
post by Gillermo: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/b4zmtDKeIe8)
Divide string to rows?
How do I divide a string in a cell to multiple cells?

Example:
String in a single cell = GOOGLE

Result (each new line is a new row and cell):
G
O
G
L
E


My guess is that it is accomplishable with ArrayFormula, MID and LEN but I can't make it work.
------------------------------------------------------------------------------------------------------------------------------------

following is a solution using ARRAYFORMULA, MID. and LEN functions

yogi_Conditional Formatting For Rows If Column A Has 'DA' And Any Of The Columns B C D Is Blank

                                         Google Spreadsheet   Post  #1610
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2014
post by Megan Cook: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Es8WX9qpDzU)

this solution supplements the solution presented in my following blog post:

yogi_Mark YES Row By Row If Column A Has 'DA' And Any Of The Columns B C D Is Blank
http://yogi--anand-consulting.blogspot.com/2014/04/yogimark-yes-row-by-row-if-column-has.html

yogi_Mark YES Row By Row If Column A Has 'DA' And Any Of The Columns B C D Is Blank

                                         Google Spreadsheet   Post  #1609
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2014
post by Megan Cook: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Es8WX9qpDzU)
Finding Blank Cells

Can I create a formula that will tell me if one cell out of a range is blank, while also filtering so that one column meets a criteria?

For example, I attached a screen shot of a sample sheet. Would there be a way to set up a formula that references the data in column A1:D8 and returns an error message if column A = "DA" and any of the other columns are blank. I don't need to know what information is missing. 

Basically, I want to add something to a Dashboard that would indicate that the information is not complete and which magazine has incomplete information. It wouldn't matter if it was one cell or twenty. Just something that would indicate to the editors that they need to go back in double check their work. 

Is that even possible?

Attachments (1)

Screen Shot 2014-04-22 at 5.03.57 PM.png

45 KB   View   Download

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