Subtotal Ifs

[sstilwell]

I have tried the multiple solutions on this forum and can not get it to work.

Use-Case: I want to sum a total amount of cells on the IF statement that they are considered a "Renewal" and that they are within the dates of 2/1-2/28/2012. So 2 if clauses, or 3 if I have to specify the 2/1/2012 & < 2/28/2012 separately.

I have tried the Sumproduct(Sumtotal and couldnt get it to work.u

I also have created a SumIFS Statement that is based off of dates. Looks like this =SUMIFS(C5:C305,L:L,"Renewal",I:I,">="&G386,I:I,"<="&H386)

Where the amounts I want to "sum" is in C5:C305, with the clause of being "Renewal" text in column L, also with the clause of being b/t the dates 2/1/2012-2/28/2012 (which I have designated as G386=2/1/2012 & H386=2/28/2012)....

Please help me out to learn and/or create a formula using the sumproduct(sumtotal) formula using my cells data. Also what does the "--" text do in a formula?

 

[PietBom]

You can try my test file:

http://www.mijnbestand.nl/Bestand-FHYVYSWKDUJS.xls

k.r.
Piet

 

[PietBom]

@sstillwell,

Could you test my file ?

k.r.
Piet

 

[T. Valko]

I have tried the multiple solutions on this forum and can not get it to work.

Use-Case: I want to sum a total amount of cells on the IF statement that they are considered a "Renewal" and that they are within the dates of Where the amounts I want to "sum" is in C5:C305, with the clause of 2/1-2/28/2012. So 2 if clauses, or 3 if I have to specify the 2/1/2012 & < 2/28/2012 separately.

I have tried the Sumproduct(Sumtotal and couldnt get it to work.u

I also have created a SumIFS Statement that is based off of dates. Looks like this =SUMIFS(C5:C305,L:L,"Renewal",I:I,">="&G386,I:I,"<="&H386)

being "Renewal" text in column L, also with the clause of being b/t the dates 2/1/2012-2/28/2012 (which I have designated as G386=2/1/2012 & H386=2/28/2012)....

Please help me out to learn and/or create a formula using the sumproduct(sumtotal) formula using my cells data. Also what does the "--" text do in a formula?

Try this...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C5,ROW(C5:C305)-ROW(C5),0)),--(I5:I305>=G386),--(I5:I305<=H386),--(L5:L305="renewal"))

 

[murrayty]

Are you asking this because the sumifs isn't working?

If so it is because your array ranges are different. Either sum column has to be C:C or the other ones need to be rows 5 to 305.

Sumproduct would be the following, change ranges 5-305 if that is what you want

=SUMPRODUCT(C:C*(I:I>=G386)*(I:I<=H386)*(L:L="Renewal"))

 

[T. Valko]

Also what does the "--" text do in a formula?

Ooops!

Missed that part.

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[sstilwell]

Thank You all for your responses. The SumProduct formulas did not work but I was able to do it using Sumifs. Not sure why I couldn't before. Must have just not had a comma in the right place.

Thank You all for your help.

I have a hyper linking/Data Validation question as well I need help with. I created a new topic for it though.

http://www.mrexcel.com/forum/showthread.php?t=609775

Thanks Again!

 

[weav8060]

another solution.
1 each data row in the table or spreadsheet contains the following counting formula applied to any populated cell in that row. I will yield 1 if the row is visible
subtotal(103,[some populated cell or column reference])
2 instead of a top level subtotal function, use a normal sumifs function that checks, among other criteria, for the above value to equal 1

 

[PietBom]

You replied on a 8,5 years old topic.
What is your new insight / improvement ?