yogi_Compute Quantity Shipped To All Locations Except As Noted On Specified Date

                                          Google Spreadsheet   Post  #1252
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 19, 2013
user Dave Fedigan (http://productforums.google.com/forum/?zx=i6lqviy57p9k#!category-topic/docs/spreadsheets/HS0eSum5WUk)
Using SumIF using multiple Criteria
Hey,

I want to use SUMIF function using multiple criteria. Here is an example. I want to see how much quantity arrived on 7/1/2013 besides the NY locations. So I want to exclude anything from the Column A that contains "NY". The amount should be 75k, but I can't get the formula to work.

 Here is the formula I'm using.=ARRAYFORMULA(SUMIF(B:B & A:A, "7/1/2013" &"<>NY*",'C:C)). Can somebody please provide a fix. Thanks.

To (Column A)                    Date (Column B)                 Quantity (Column C)

Boston                                7/1/2013                               25,000

Boston                                7/1/2013                               25,000

Philadelphia                         7/1/2013                               25,000

NY (JFK)                             7/1/2013                               25,000

NY (Kennedy)                      7/1/2013                               25,000

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

yogi_Count Instances Of Numbers In Column B By Week For Dates in Column A

                                          Google Spreadsheet   Post  #1251
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 18, 2013
user MeBjammin (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Y9XaohyCAfw)
countunique returns a 1
Hello All,

I was wondering if maybe you might have a better idea of a work around as what I came up with seems to me like it should not work.

I have an array of data where column A contains a time stamp (unique times, no repeats), and column B contains either nothing (a formula that yields a blank) or any integer from 0 to 5.

What I want is a way to see how many occurrences (time stamps) I have all total and how many occurrences of each number (don't need to count blanks, just how many zeros or ones or twos, so on) all within a time frame.

I used this code for the date:

=countunique(filter($A$2:$A, $A$2:$A>=$D6, $A$2:$A<$D5, $A$2:$A<>""))

and this one for the number (replacing the 0 at the end with 1, 2, 3, 4 or 5):

=countif(filter($B$2:$B, $A$2:$A>=$D6, $A$2:$A<$D5, $B$2:$B<>""), 0)

which works, however, if there were NO time stamps for a given time frame (D5 to D6) then instead of returning 0 or nothing, it returned a 1 for the date, and reported 1 zero. Which doesn't work as we have dead weeks where there are no time stamps recorded but we need to see the over all patterns from week to week over a month or so.

I then used this:

=if(filter($A$2:$A, $A$2:$A>=$D14, $A$2:$A<$D13, $A$2:$A<>"")="","",countunique(filter($A$2:$A, $A$2:$A>=$D14, $A$2:$A<$D13, $A$2:$A<>"")))

=if(filter($B$2:$B, $A$2:$A>=$D14, $A$2:$A<$D13, $B$2:$B<>"")="","",countif(filter($B$2:$B, $A$2:$A>=$D14, $A$2:$A<$D13, $B$2:$B<>""), 0))

which instead of returning a 1 returns a #N/A with error: No valid data

adding iferror:

=iferror(if(filter($A$2:$A, $A$2:$A>=$D22, $A$2:$A<$D21, $A$2:$A<>"")="","",countunique(filter($A$2:$A, $A$2:$A>=$D22, $A$2:$A<$D21, $A$2:$A<>""))))

=iferror(if(filter($B$2:$B, $A$2:$A>=$D22, $A$2:$A<$D21, $B$2:$B<>"")="","",countif(filter($B$2:$B, $A$2:$A>=$D22, $A$2:$A<$D21, $B$2:$B<>""), 0)))

at least returns a blank (or whatever else I want)

however, all three of these codes (primarily the second and even more so the third) seem a bit superfluous especially when trying to edit them. there has to be a better way.

here is a mock up:

https://docs.google.com/spreadsheet/ccc?key=0AjIduJt8dPWqdHVFU3FVcnBTSUZFOFhwclJQSlJyNUE#gid=0

it contains time stamps in A2:A with their respective scores in B2:B. A representation of each formula style is there as well as a manually counted and a version pulling from a second tab (which is how it will have to work in the actual doc).

Any ideas would be great, thank you!

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

yogi_Compute Extract The Lowest Total From A Number Of Bid Amounts in Column B

                                          Google Spreadsheet   Post  #1250
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 18, 2013
user Andrew Hazelwood13 (http://productforums.google.com/forum/#!category-topic/docs/how-do-i/Cu2RQdTLus8)
Would it be possible to do as I asked in the title? (Using Firefox on an Windows XP OS). Let me elaborate a touch. Say we have the following data:

Cell #     Value
1               7
5               5
7               9
13             3
27             8

So in this case, the data is all in the same column, but spread out vertically. Is it possible to choose certain cells to compare, then output the lowest number? So in this case, it would compare all 5 cells and choose cell # 13 to put the value of 3 into another cell for me. I have some basic formula knowledge from Excel, but it's very basic and I can't seem to remember anything pertaining to this. Hopefully it's possible! Thank you in advance for responses!!

Andrew Hazelwood
---
Yogi,

My apologizes in not making the data realistic but simple. Here is a link to a new spreadsheet with the new (and simplistic) data.

https://docs.google.com/spreadsheet/ccc?key=0Ai7BHC-ihy1YdExsQlMyUU5PMUpqVFc1NXpXRExUNXc#gid=0

A) Tell us what you need help with.
          As I said before, I want to compare several summations in order to determine which summation provides the LOWEST sum, and then use that sum in a simple calculation (just multiplying it by 2).

B) In which cell? Of which sheet?
          In the new spreadsheet provided, the three summations are in Sheet1, cells B6, B12 and B18. I would like to compare the 3 and have the spreadsheet automatically write that summation in cell E2 and multiply it by 2.

C) Show us your expected result.
          The three summations are 15, 22 and 18 (respective to the cell numbers provided above). The spreadsheet would compare them, find that the first summation of "15" is the lowest, then multiply it by 2 to get "30", which would appear in cell E2.

Hope I gave you what you needed this time! Sorry for the delays and mix ups.

Andrew
---------------------------------------------------------------------------------------------------------------------------

yogi_Compare Data In Column B With That In Column A And if Not Present Post In Column C And ...

                                          Google Spreadsheet   Post  #1249
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 18, 2013
user EVRoberts (https://productforums.google.com/forum/#!msg/docs/2_qARI3wBfE/0L3yx_pXK8IJ)
Help Comparing Multiple Columns vs a Single Column and creating a list of Uniques
Hi Google Doc'rs, I'm hoping you can help.

I have a Base Column, Column A and I'm trying to compare the other columns to it

A & B find the values that are in B but not in A, post them to column C

A & D find the values that are in D but not in A, post them to column E

A & F find the values that are in F but not in A, post them to Column G

I've tried a couple of formulas to do something similar which you can see in C2 and G2

G2 formula worked but wasn't consistent. When I checked it was falsely reporting some No's and Yes'

Any suggestions?

---

whoops i forgot that part

https://docs.google.com/spreadsheet/ccc?key=0AgWdku43C6i9dGVYQjJnX2ZTaVEySHJud0lweXJDd0E&usp=sharing

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

yogi_WorkAround For Formating A Date in A Style Not Available Via More Formats For The Related Spreadsheet Locale

                                          Google Spreadsheet   Post  #1248
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 18, 2013
user rajesh kudesia (http://productforums.google.com/forum/?zx=qbs7kko366y6#!category-topic/docs/spreadsheets/o_xl7G5LIQM)
I can't see date format 26 Sept 2008
When I click Format-Numbers-More Formats, I don't see all date formats. Only limited formats I see. I need 26 Sept 2008. But I don't see it.I use Windows XP and tried Firefox and chrome both. I deleted history too.
---
David,

I see only three formats i.e. 26/09/08, 26/09 and time 15.59. I don't see '26 Sept 2008'. Sheet is made in this month only. Spreadsheet settings are India.

---

Varma Ji,

Thanks, it works. But this way I have to enter each date in each cell. It does not format the column. So big job for my staff.

Rajesh

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

yogi_Set Up Formula To Automatically Calculate Row by Row WeekDay Month And Day In Columns C D And E As Dates Are Entered In Column B

                                          Google Spreadsheet   Post  #1247
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 17, 2013
user Coach Bill (http://productforums.google.com/forum/?zx=qbs7kko366y6#!category-topic/docs/spreadsheets/MUaf3fp14z8)
Greetings,

I've been reading several posts about auto copy, but they all use arrayformulas() and i'm not that advanced, so i'm going to ask my question a little differently.

Goal:  Eliminate unnecessary formulas down the entire row, but rather have them auto copy when data in the first column is added to a new row.

Data:  In my Sheets document i have the following:

COLUMN A - Manually entered date

COLUMN B - Formula (see below) would return the Day of the Week (i.e. Saturday)

A         B

1 DATE DAY

2 6/1/2013 =IF(A2<>0,TEXT(A2,"dddd"),"")

3

I want the formula in B2 to auto copy to B3 if A3 contains any value other than 0

Is this even possible or am I completely over-complicating this?

Thanks

Coach

---

Here is what you asked and my responses:

a) tell us what you need help with

I need help adding a formula or script that will auto-add formulas to the next row when a new date value is entered so that i can avoid unnecessary formulas all the way down the entire sheet.  * (As a bonus, it would also be great to do the same thing with the validation cells whereby they are copied down as a new date is added.  This would eliminate the grey down-arrows from populating all the way down the column.)

b) in which cell?

Columns B-E

c) of which sheet?

Test Sheet

d) show us your expected result along with needed logic/explanation

The expected result is not to have the formulas copied down the entire column.

The current formula is the noted columns is current and correct.

The link to the file is below:

https://docs.google.com/spreadsheet/ccc?key=0Arkt3KnTUsuHdFpfOG5ZMEdyX1JvYXJnel9WUGw3dFE&usp=sharing

Thanks

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