Solved: SUMIF "criteria"

[anovice]

Hi

I'm using the following formula :

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,"1",'Sales Invoices Journal'!H$3:H$102)

What I'm trying to do is copy this formula down rows say A1:A100 but I would like the "1" to increase by 1 each time.

So CellA1
=SUMIF('Sales Invoices Journal'!$D$3:$D$102,"1",'Sales InvoicesJournal'!H$3:H$102)
CellA2
=SUMIF('Sales Invoices Journal'!$D$3:$D$102,"2",'Sales Invoices Journal'!H$3:H$102)
CellA3
=SUMIF('Sales Invoices Journal'!$D$3:$D$102,"3",'Sales Invoices Journal'!H$3:H$102)

and so on...

Probably abc to you guys but would appreciate your help.

[GTO]

Presuming you are wanting to do the copy down part manually:

In a Standard Module:

Option Explicit

Sub CopyFormulaDown()
Dim i As Long
Dim rCell As Range

i = 0
For Each rCell In Selection
i = i + 1
rCell.Formula = "=SUMIF('Sales Invoices Journal'!$D$3:$D$102," & _
i & ",'Sales Invoices Journal'!H$3:H$102)"
Next
End Sub



Then select the cells you want the formula in starting (by your example) in A1, or that is, starting at the cell where the criteria is 1, then...

run the macro...

Hope this helps,

Mark

[Bob Phillips]

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,ROW(A1),'Sales Invoices Journal'!H$3:H$102)

[anovice]

That does work thanks but I haven't explained myself fully.

If say Cells A1 to A6 show the following :

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,"1",'Sales Invoices Journal'!H$3:H$102)

=SUMIF('Sales Invoices Journal'!$D$108:$D$207,"1",'Sales Invoices Journal'!H$108:H$207)

=SUMIF('Sales Invoices Journal'!$D$213:$D$312,"1",'Sales Invoices Journal'!H$213:H$312)

=SUMIF('Sales Invoices Journal'!$D$319:$D$418,"1",'Sales Invoices Journal'!H$319:H$418)

=SUMIF('Sales Invoices Journal'!$D$424:$D$523,"1",'Sales Invoices Journal'!H$424:H$523)

=SUMIF('Sales Invoices Journal'!$D$529:$D$628,"1",'Sales Invoices Journal'!H$529:H$628)
There would be 6 more rows in a similar vein.

Can I copy all of these down as shown to Cells A7 to A12 and only increase the
"criteria" by increments 1 of 1each time?

So A7 to A12 would be "2"
A13 to A18 would be "3"
A19 to A24 would be "4"

I actually have 12 rows showing the above formulae and will need to copy those first 12 rows until
the "criteria" reaches "100" or maybe even more.

Hope I'm explaining myself better.

[Bob Phillips]

Use

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,INT((ROW(A1)+5)/6),'Sales Invoices Journal'!H$3:H$102)

etc.

[tkaplan]

make the criteria "CEILING(ROW()/6,1)"

[anovice]

I've attached sample workbook as I'm not explaining the problem very well.

[tkaplan]

make the criteria :
COUNTIF($B$2:$B5,$B5)

[Bob Phillips]

We understood perfectly, it just needed modifying what we gave you in the light of the REAL data.

This modification using tkaplan's suggestion (it is more obvious than mine, although both work) works on your data

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,CEILING(ROW()/20,1),'Sales Invoices Journal'!H$3:H$102)

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,CEILING(ROW()/20,1),'Sales Invoices Journal'!I$3:I$102)

=SUMIF('Sales Invoices Journal'!$D$3:$D$102,CEILING(ROW()/20,1),'Sales Invoices Journal'!J$3:J$102)

[anovice]

Wow ! Thanks ! Works just as I wanted.

[tkaplan]

xld,
the reason i put the second idea of counting what number instance of the month he is showing (first set of 12 months he wants "1", next set "2") is that this way if he adds more rows he won't need to change the forumla b/c the reference to the rows will change.
what do you think?

[Bob Phillips]

Yes, I agree, but he does need to adjust the divisor based upon the number of rows in each set. I used the same approach, just your formula was simpler than mine.

[tkaplan]

I was referring to making the criteria count the number of "January 2009": COUNTIF($B$2:$B5,$B5) so that way even if the number of rows changes, the formula does not need to be changed.

[anovice]

XLD can would you explain what you mean by "adjusting the divisor".

By the way I'm a she !

[Bob Phillips]

Sorry about that, typical male assumption :)

If you look at the formula, it includes

CEILING(ROW()/20,1)

so we have the row number divided by 20. That number, 20, should reflect the number of rows in a data group. That is why tkaplan originally used 6, we thought each block was 6 rows high.

[anovice]

Thanks for the explanation XLD.

As you may have noticed by my name I'm just learning & trying to understand how these formulae work.

Anyway it's all working great

Thanks to all :clap:

[Bob Phillips]

I actually looked at it again, and I noticed that you had extra header rows intermittently interspersed. This could throw it, because some blocks would be 20 rows and some would be 21 rows. Is it actually throwing it off further down the sets of blocks?

[anovice]

Yes it did & I realised what was happening after your divisor explanation so I just got rid of the extra rows and it sorted it thanks.

I've just had a private message from someone asking me for VBA help :rotlaugh: