|
Using SUMIF with dates
Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09,
etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? |
Using SUMIF with dates
This would be easier to use
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? |
Using SUMIF with dates
Awesome, this works great. How about for AVERAGEIF function for the same
criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . |
Using SUMIF with dates
Try this array formula** :
=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . |
Using SUMIF with dates
One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . |
Using SUMIF with dates
I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . |
Using SUMIF with dates
By not working, I mean I am getting an error message.
I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . |
Using SUMIF with dates
Try this...
With Sheet1!A:A containing true Excel dates... A1 = the month name as a TEXT entry = Jan B1 = the year number = 2009 =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B) -- Biff Microsoft Excel MVP "yowzers" wrote in message ... By not working, I mean I am getting an error message. I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . |
Using SUMIF with dates
And you'd like us to guess what the error message is? :-(
-- David Biddulph "yowzers" wrote in message ... By not working, I mean I am getting an error message. I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . |
Using SUMIF with dates
Perfect!
"T. Valko" wrote: Try this... With Sheet1!A:A containing true Excel dates... A1 = the month name as a TEXT entry = Jan B1 = the year number = 2009 =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B) -- Biff Microsoft Excel MVP "yowzers" wrote in message ... By not working, I mean I am getting an error message. I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . . |
Using SUMIF with dates
Last question, I promise.....maybe =P
I tried doing this same thing with the AVERAGEIF function and the answer is coming back 0 for some reason. Am I doing something wrong? "T. Valko" wrote: Try this... With Sheet1!A:A containing true Excel dates... A1 = the month name as a TEXT entry = Jan B1 = the year number = 2009 =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B) -- Biff Microsoft Excel MVP "yowzers" wrote in message ... By not working, I mean I am getting an error message. I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . . |
Using SUMIF with dates
I figured out the answer to the question below on my own. Now I need to add
a 0 function to both the SUMIF and AVERAGEIF functions as well as the MONTH and YEAR "yowzers" wrote: Last question, I promise.....maybe =P I tried doing this same thing with the AVERAGEIF function and the answer is coming back 0 for some reason. Am I doing something wrong? "T. Valko" wrote: Try this... With Sheet1!A:A containing true Excel dates... A1 = the month name as a TEXT entry = Jan B1 = the year number = 2009 =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B) -- Biff Microsoft Excel MVP "yowzers" wrote in message ... By not working, I mean I am getting an error message. I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . . |
Using SUMIF with dates
I am using Excel 2007.
Now I need to add a 0 function =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),--(Sheet1!$B:$B0),Sheet1!$B:$B) Array entered: =AVERAGE(IF(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1,IF( Sheet1!$B:$B0,Sheet1!$B:$B))) Even though you're using Excel 2007 you should still use as small a range as possible. Both of those formulas will evaluate *EVERY* cell in the referenced columns. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... I figured out the answer to the question below on my own. Now I need to add a 0 function to both the SUMIF and AVERAGEIF functions as well as the MONTH and YEAR "yowzers" wrote: Last question, I promise.....maybe =P I tried doing this same thing with the AVERAGEIF function and the answer is coming back 0 for some reason. Am I doing something wrong? "T. Valko" wrote: Try this... With Sheet1!A:A containing true Excel dates... A1 = the month name as a TEXT entry = Jan B1 = the year number = 2009 =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B) -- Biff Microsoft Excel MVP "yowzers" wrote in message ... By not working, I mean I am getting an error message. I am using Excel 2007. Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in text and B1 would say "2009" in text. Basically I want a sum if the dates in column A:A are Jan AND 2009. Thanks for your help. "T. Valko" wrote: I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) Specifically, what does "doesnt seem to work" mean? One thing, unless you're using Excel 2007 you can't use entire columns as range references. What's in A1? What's in B1? Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will return an error. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... One more question. How about if I want another criteria? In the example above, I want it to sort by Month, but how about if i want to sort by Month and Year? I tried this formula and it doesnt seem to work. =SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B) "T. Valko" wrote: Try this array formula** : =AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "yowzers" wrote in message ... Awesome, this works great. How about for AVERAGEIF function for the same criteria? "Peo Sjoblom" wrote: This would be easier to use =SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150) change the cell references to fit your data then copy down -- Regards, Peo Sjoblom "yowzers" wrote in message ... Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if column C in sheet 1 equals column A in sheet 2. Is there anyway I can put this all into one formula so I don't have to have that column C in sheet 1? . . . . |
| All times are GMT +1. The time now is 05:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com