Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF? SUMIF? SUMPRODUCT? IF?
hi,
Here is a data: Month completed Year completed Past Due? Name 7 2007 Y Joe 7 2007 N Allie 7 2007 Y Allie 7 2007 N John 8 2007 N Mark 8 2007 N Amanda 9 2007 Y John 10 2007 Y Amanda 11 2007 Y John 11 2007 N Allie 4 2008 Y Joe 3 2008 N Mark 6 2008 N Amanda 6 2008 Y Amanda 5 2008 N Allie 1 2008 N John I was wondering if I could have a function that would sort the number of completions done per month FOR THE FISCAL YEAR 2008 ONLY where the fiscal year starts from November1st. So for ex. the fiscal year of 2008 starts on November 1st 2007. So, like(according to the list above i need to have the following for "Number of Reviews for FY2008"): Month Number of Reviews for FY2007 Number of Reviews for FY2008 Nov 0 2 Dec 0 0 Jan 0 1 Feb 0 0 Mar 0 1 Apr 0 1 May 0 1 Jun 0 2 Jul 4 - Aug 2 - Sep 1 - Oct 1 - so far I have =IF($D:$D=2007,COUNTIF(B:B,11),0) where column B and D are the Month and Year completed respectively. It works for half of the months but the other half it doesn't Secondly, I would like to make another list that would sort it so that it tells the number of past dues done per month. So: Month Past Dues for Fiscal Year 2008 Nov 1 Dec 0 Jan 0 Feb 0 Mar 0 Apr 1 May 0 Jun 1 Jul 0 Aug 0 Sep 0 Oct 0 i tried all these: =IF(AND(B:B=7,D:D=2007),COUNTIF(E:E,Y),0) =COUNT(AND(COUNTIF(B:B,7),COUNTIF(D:D,2007),COUNTI F(E:E,Y))) =(IF(AND(E:E="Y",D:D=2007),0,COUNTIF(B:B,7))) =SUM(--(D:D=2007),--(B:B=7),--(E:E="Y")) =IF(E:E="Y",0,IF(D:D=2007,COUNTIF(B:B,7),0)) =SUM((B:B<7)*(D:D<2007)*(E:E="Y")) and a lot of other things. I just can't seem to figure out the Past Dues one. I need to have a CountIF with multiple criterias, or a sumproduct or something like that help please! appreciate it! omss |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF? SUMIF? SUMPRODUCT? IF?
Try like this:
SUMPRODUCT((B1:B100=7)*(D1:D100=2007)*(E1:E100="Y" )) You can't use a whole column, unless you are using xl-2007 "omss" wrote: hi, Here is a data: Month completed Year completed Past Due? Name 7 2007 Y Joe 7 2007 N Allie 7 2007 Y Allie 7 2007 N John 8 2007 N Mark 8 2007 N Amanda 9 2007 Y John 10 2007 Y Amanda 11 2007 Y John 11 2007 N Allie 4 2008 Y Joe 3 2008 N Mark 6 2008 N Amanda 6 2008 Y Amanda 5 2008 N Allie 1 2008 N John I was wondering if I could have a function that would sort the number of completions done per month FOR THE FISCAL YEAR 2008 ONLY where the fiscal year starts from November1st. So for ex. the fiscal year of 2008 starts on November 1st 2007. So, like(according to the list above i need to have the following for "Number of Reviews for FY2008"): Month Number of Reviews for FY2007 Number of Reviews for FY2008 Nov 0 2 Dec 0 0 Jan 0 1 Feb 0 0 Mar 0 1 Apr 0 1 May 0 1 Jun 0 2 Jul 4 - Aug 2 - Sep 1 - Oct 1 - so far I have =IF($D:$D=2007,COUNTIF(B:B,11),0) where column B and D are the Month and Year completed respectively. It works for half of the months but the other half it doesn't Secondly, I would like to make another list that would sort it so that it tells the number of past dues done per month. So: Month Past Dues for Fiscal Year 2008 Nov 1 Dec 0 Jan 0 Feb 0 Mar 0 Apr 1 May 0 Jun 1 Jul 0 Aug 0 Sep 0 Oct 0 i tried all these: =IF(AND(B:B=7,D:D=2007),COUNTIF(E:E,Y),0) =COUNT(AND(COUNTIF(B:B,7),COUNTIF(D:D,2007),COUNTI F(E:E,Y))) =(IF(AND(E:E="Y",D:D=2007),0,COUNTIF(B:B,7))) =SUM(--(D:D=2007),--(B:B=7),--(E:E="Y")) =IF(E:E="Y",0,IF(D:D=2007,COUNTIF(B:B,7),0)) =SUM((B:B<7)*(D:D<2007)*(E:E="Y")) and a lot of other things. I just can't seem to figure out the Past Dues one. I need to have a CountIF with multiple criterias, or a sumproduct or something like that help please! appreciate it! omss |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF? SUMIF? SUMPRODUCT? IF?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
sumif, countif, sumproduct????? | Excel Worksheet Functions | |||
Sumproduct, If, Sumif, Countif, Match?? | Excel Discussion (Misc queries) | |||
countif, sumif, sumproduct - I dont know which to use | Excel Worksheet Functions | |||
Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) |