SumIF Question

  • Thread starter Thread starter RF
  • Start date Start date
R

RF

I have some columns that look like the following:

x 3 9
x 4 2
6 3

x 8 7
9 8

x 13 3
75 4

x 2 2
x 3 7
11 5
13 9

x 4 1

I put the following formula in cell D1:

=IF(c3="","",IF(c2="",SUMIF(a3:a100,"=x",c3:c100)))

This formula tells me the total of all cells in the C row that
correspond to an X in the A row--all the way down. So far so good.

Each group is separated by a space, because each group represents a
different day. I want to find the total of the data in the C column
that also has an X in the A column for each individual group.

I want to find the sum of the first group of three, that also has an
x, the the second group of two, third group of two-- all the way down.

In other words, how did my Xs do today? How did they do yesterday, et
al.

Thanks
 
try this macro

Sub sumifgroups()
r1 = Cells(2, 2).Row
On Error GoTo quitit
doit:
r2 = Cells(r1, 2).End(xlDown).Row
MsgBox Application.SumIf(Range(Cells(r1, 1), Cells(r2, 1)), _
"x", Range(Cells(r1, 3), Cells(r2, 3)))
r1 = r2 + 2
GoTo doit
quitit:
End Sub
 
RF said:
I have some columns that look like the following:

x 3 9
x 4 2
_ 6 3

x 8 7
_ 9 8

x 13 3
_ 75 4

x 2 2
x 3 7
_ 11 5
_ 13 9

x 4 1
Click to expand...
....

I'll assume the topmost row of this would be in A3:C3.
Each group is separated by a space, because each group represents a
different day. I want to find the total of the data in the C column
that also has an X in the A column for each individual group.
Click to expand...
....

So one formula for each group/day? If so, try this.

D3 [array formula]:
=IF(C2="",SUMIF(A3:INDEX(A3:A$100,MATCH("",C3:C$100&"",0)-1),"x",C3),"")

Fill D3 down into D4:D100. This assumes col C would always have numbers in
it except for the blank rows between groups/days. This displays results in
the topmost row of each group/day, which it determines by checking whether
the cell in col C in the row above evaluates to "". It constructs a dynamic
range in col A from the current row down to the row above the next cell in
col C that evaluates to "". The tricky bit is that SUMIF automatically
resizes its third argument, C3, to the same size as its first argument.
 
Hi and thanks.

If I want to use this macro do I just click on the cell I want to
apply it? Then I right click on the worksheet name?

Sorry, but I'm not too fluent with macros.

Thanks again
 
Back
Top