What's the formula to do SUMIF within Date Range and with one additional criteria

[upfish]

Hi all! I am trying to perform SUMIF within a certain date range, with one additional criteria. For example, for plan type=NQ and within Date Range: 4/01/2013 - 09/30/2013, I want to find out the total sum on the "Option Granted" column.

Plan Type Grant ID Grant Date Options Granted Option Price
NQ 0000000000002 6/31/2013 200 20.5625
NQ 0000000000003 4/15/2013 200 20.5625
NQ 0000000000004 7/10/2013 74 71.54
NQ 0000000000005 1/31/2000 200 20.5625
NQ 0000000000006 6/10/1998 268 44.27
BQ 0000000000009 8/18/2012 3000 25
BQ 0000000000010 8/18/2013 2500 25
BQ 0000000000011 8/18/2013 3500 25

I am currently using this formula, but it does not apply the plan type=NQ criteria, does anyone know how to apply plan type=NQ as well?

=SUMIFS('Aggregate Detail Results'!$D$5:$D$61791,'Aggregate Detail Results'!$C$5:$C$61791,">="&DATE(2013,4,1),'Aggregate Detail Results'!$C$5:$C$61791,"<"&DATE(2013,9,30))

Many thanks!

 

[Weazel]

maybe...

=SUMIFS('Aggregate Detail Results'!D2:D9,'Aggregate Detail Results'!A2:A9,"NQ",'Aggregate Detail Results'!C2:C9,">="&DATE(2013,4,1),'Aggregate Detail Results'!C2:C9,"<="&DATE(2013,9,30))

also, there are only 30 days june so 6/31 isn't recognized as a date

 

[cyrilbrd]

Hi all! I am trying to perform SUMIF within a certain date range, with one additional criteria. For example, for plan type=NQ and within Date Range: 4/01/2013 - 09/30/2013, I want to find out the total sum on the "Option Granted" column.

Plan Type Grant ID Grant Date Options Granted Option Price
NQ 0000000000002 6/31/2013 200 20.5625
NQ 0000000000003 4/15/2013 200 20.5625
NQ 0000000000004 7/10/2013 74 71.54
NQ 0000000000005 1/31/2000 200 20.5625
NQ 0000000000006 6/10/1998 268 44.27
BQ 0000000000009 8/18/2012 3000 25
BQ 0000000000010 8/18/2013 2500 25
BQ 0000000000011 8/18/2013 3500 25

I am currently using this formula, but it does not apply the plan type=NQ criteria, does anyone know how to apply plan type=NQ as well?

=SUMIFS('Aggregate Detail Results'!$D$5:$D$61791,'Aggregate Detail Results'!$C$5:$C$61791,">="&DATE(2013,4,1),'Aggregate Detail Results'!$C$5:$C$61791,"<"&DATE(2013,9,30))

Many thanks!

Given that NQ and BQ are in Column A, have you tried:
=SUMIFS('Aggregate Detail Results'!$D$5:$D$61791,'Aggregate Detail Results'!$A$5:$A$61791,"=NQ",'Aggregate Detail Results'!$C$5:$C$61791,">="&DATE(2013,4,1),'Aggregate Detail Results'!$C$5:$C$61791,"<"&DATE(2013,9,30))

 

[upfish]

Worked! Thanks much. Also used it in countifs. What's the difference between COUNTIFS AND COUNTIF?

Given that NQ and BQ are in Column A, have you tried:
=SUMIFS('Aggregate Detail Results'!$D$5:$D$61791,'Aggregate Detail Results'!$A$5:$A$61791,"=NQ",'Aggregate Detail Results'!$C$5:$C$61791,">="&DATE(2013,4,1),'Aggregate Detail Results'!$C$5:$C$61791,"<"&DATE(2013,9,30))

 

[cyrilbrd]

Most welcome. Thanks for the feedback.

Worked! Thanks much. Also used it in countifs. What's the difference between COUNTIFS AND COUNTIF?

COUNTIF is one criterion.
COUNTIFS has up to 127 criteria.