yogi_Sum Up A Column In A Table Based On Multiple Criteria

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #586    Jun 10, 2012     www.energyefficientbuild.com.

user Ian*R said:
Adding a column based on matching three criteria
Hi--

I want to sum column E if the following criteria are matched:

The date in Column B is on or after 1/1/2012

The date in Column B is also on or before 1/31/2012

The Category in Column A equals "Donations"

I've tried using SUMIF: 

=SUMIF(Items!B2:B897&Items!B2:B897&Items!A2:A897;">=1/1/12"&"<=1/31/12"&"Donations";Items!E2:E897)

I've also tried SUM and FILTER:

=SUM(FILTER(Items!E:E,Items!B:B>="1/1/12"&Items!B:B<="1/31/12",Items!A:A="donations"))

Neither gets me what I want.

Can anyone help?

Thanks,

Ian

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

following is a solution to the problem

yogi_Extract Items From A List Based On Items In One Column Meeting A DropDown And In The Other Column Not Meeting Another DropDown

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #585    Jun 09, 2012     www.energyefficientbuild.com.

user blockscott said:
Google Spreadsheet Help needed
I want to be able to make a spreadsheet with a header row that has pull downs to pick from to sort the rows below it.  If anyone has or knows of a template that i can edit that would be awesome, if not any help would be appreciated.

So, what i would like to be able to do is from lets say Row 4 Column 1, I want to pick from a pulldown that has 3 options (lets call them: Red, Yellow, and Blue).  So if i pick Yellow from the pull down, it displays all rows below that have Yellow in column 1.  Then say i have a pull down in Row 4 Column 2 that has 2 options (lets call them: good and bad).  So now if i pick Good from the pull down in row 4 column 2, it displays all Rows with Yellow in column 1 and Bad in column2.

anyones help in this would be very much appreciated, Thanks

----------------------------------------------------------------------
following is a solution to the problem

Yogi_Query A Range With Query Criteria Also Being A Range

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #584    Jun 08, 2012     www.energyefficientbuild.com.


user VP4711 said:
Query a range with query criteria is also a range
My spreadsheet looks like this

         A               B                 C           D

1     Car1        BMW             Car1      =query(?????)

2     Car2        Mercedes       Car3

3     Car3        Porsche         Car7

4     Car4        Hyundai

5     Car5        Honda

6     Car6        Ford

7     Car7        Ferrari

 I do not want to use vlookup in each column D1, D2, D3. I want to use Query function to show the list in D1:D3 basis criteria in C1:C3

Formula might be

Query (A1:C7; "Select B where A = C1:C3")

 and desired result is

        A         B               C          D

1     Car1        BMW             Car1       BMW

2     Car2        Mercedes        Car3       Porsche

3     Car3        Porsche         Car7       Ferrari

4     Car4        Hyundai

5     Car5        Honda

6     Car6        Ford

7     Car7        Ferrari

 Please tell me what am I doing wrong?

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

you can use contain in QUERY Select as illustrated in

following solution to the problem

yogi_Rearrange A Table Of Appointment Not Sorted By Time Into A Table Of Appointments And Time To Next Appointment

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #583    Jun 08, 2012     www.energyefficientbuild.com.

user Chematronix said:
Formula: how to obtain the closest match of a cell in its same column?
Howdy,
I want to find the closest match of a value in its same column.  Specifically, I've a column of Times (say, appointments), and I want another of Time Before Next Appointment.  For this I need to find the next appointment relative to the current row, and subtract the current time to obtain the difference.  The tricky part is, the sheet is not sorted by Time, so I can't simply subtract the current value from next row's.
Seems VLOOKUP should help, but it also would require sorting by Time; also, since I'm searching for a value in its same column, it returns that very same value. Is there an easy way to duplicate the target range, exclude the current row from it, and sort it by Time?
Or perhaps there's another easy way to accomplish this simple calculation?  Get the range as an array, sort the values and search for one that is bigger?
Here's my test sheet, feel free to add columns with new methods: 
https://docs.google.com/spreadsheet/ccc?key=0AsSi-TI7wOMqdFdxdm1aRHdfWDFqdjFLcnBWNFhxS1E#gid=0
I'm going to check scripting next, as hopefully that will provide much more flexibility (not to mention readability) than clunky formulas.  There must be a way to trigger a function to update a column every time a certain range is edited, right?
Any help is appreciated.
---------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Compare ListA And ListB And Extract 1) All And 2) ExactMatch ListA Items That Are Also In ListB

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #582    Jun 08, 2012     www.energyefficientbuild.com.

user Derges said:
Comparing two lists of strings
I'm probably being quite stupid here. I need to compare two lists of strings and determine which items in List A are also in List B.

I've tried EXACT and MATCH and a HLOOKUP but I think I've got the wrong end of the syntax stick.

Would anyone be kind enough to offer some advice?

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

following is a solution to the problem

by the way I don't think OP meant finding Exact matches even though the OP talked about using the Exact function, I have gone ahead with finding 1) All matches as well as 2) Exact matches only

yogi_Rearrange Unique Data In Columns Of A Table By Entity In First Column Of The Table

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #581    Jun 07, 2012     www.energyefficientbuild.com.

the convoluted solution herein was pronmpted by a question like this in Google Docs Help forum

yogi_Compute Stats By Month From A DropDown And By Year for Values In A Table

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #580    Jun 05, 2012     www.energyefficientbuild.com.

user Smileyninja said:
Trying to filter based on month
https://docs.google.com/spreadsheet/ccc?key=0AhckPttGQAT1dHVnQktJaDNGRUxrelBsMHdkZ3ZVQUE

I would like to be able to select a month in the dropdown and it would filter the schedule by the month. Ideally, I could then export to a google calendar.  I feel I'm 70% of the way to get this to work.

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

following is a solution to the problem