SumIF Question  
Author Message
Amac0714





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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