Friday, June 29, 2012

yogi_Count The Entities And Their Related Sums By Month And Year

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

user Kelemvor said:
How can I tally up items from one sheet into a summary on another?
Sample: 
https://docs.google.com/spreadsheet/ccc?key=0Ajg2ipGcRDpSdDNTazJHTXFZMHUyeUZ0Z1ozRG5sRWc
OK, so I got the totally of the items to work via this: 
=SUM(FILTER( Data!C$2:C ; (Data!E$2:E)=A3 ; YEAR(Data!A$2:A)=2012 ))
I took the month portion of the date in Data!A2 and put that in Data!E2 and then compared that to the text in the summary sheet.
Probably an easier way so let me know but it's at least working for now.
However, I haven't gotten the count part to work.  All I want to do is count how many entries there are for each month/year.
I tried this:
=COUNTIF ((Data!E$2:E100),A5 )  But it didn't work.
Column E in the Data sheet contains the months of the dates (January, February, etc)
Cell A5 in the Summary sheet contains the word August.
So if there are any entries in Data!E that say August, it should count them...  But it just shows me 0.
hmmmm
----------------------------------------------------------------------------------------------
following is a solution using the QUERY function


No comments:

Post a Comment