SumIF Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RF - 05 Sep 2007 15:22 GMT

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

Reply to this Message

Don Guillett - 05 Sep 2007 16:02 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have some columns that look like the following:
>
[quoted text clipped - 33 lines]
>
> Thanks

Reply to this Message

RF - 07 Sep 2007 01:53 GMT

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

>try this macro
>
[quoted text clipped - 9 lines]
>quitit:
>End Sub

Reply to this Message

Harlan Grove - 06 Sep 2007 07:04 GMT

"RF" <Farmer@FarmerRon.com> wrote...

>I have some columns that look like the following:
>
[quoted text clipped - 14 lines]
>
>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.

Reply to this Message