## Wednesday, April 30, 2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-30-2014
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.
----------------------------------------------------------------------------------------------------------------------------------------------------

## Sunday, April 27, 2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
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.

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

## Saturday, April 26, 2014

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

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

---

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'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
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)

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)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
How do you combine like information with query and sum?
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'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
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.

thanks
----------------------------------------------------------------------------------------------------------------------------------------------------------
I have presented the solution in sheet named yogi_RIEPILOGATIVO

## Friday, April 25, 2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2014
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2014

## Thursday, April 24, 2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
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

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
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

## Tuesday, April 22, 2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2014