|
Author |
Message |
Amac0714
|
Posted: Wed Sep 05 08:02:35 PDT 2007 |
Top |
Excel >> SumIF Question
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
Excel264
|
|
|
|
|
Don
|
Posted: Wed Sep 05 08:02:35 PDT 2007 |
Top |
Excel >> SumIF Question
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
>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
|
|
|
|
|
Harlan
|
Posted: Wed Sep 05 23:04:18 PDT 2007 |
Top |
Excel >> SumIF Question
>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'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.
...
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.
|
|
|
|
|
RF
|
Posted: Thu Sep 06 17:53:49 PDT 2007 |
Top |
Excel >> SumIF Question
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
On Wed, 5 Sep 2007 10:02:35 -0500, "Don Guillett"
>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
|
|
|
|
|
|
|