Help with SUMIF

[sumitk]

Hi. I need to be able to do a summary report where I need to add all the
numbers if the row and column headers both meet certain criteria. For
example, in the table below, I would like to get a value for the combination
Cat/White (i.e. 5+35 = 40) or Dog/Brown (i.e. 30)

Row/Col. A B C D
1 Animal/Color White Black Brown
2 Cat 5 10 15
3 Dog 20 25 30
4 Cat 35 40 45

In this particular situation, I do not want to use a pivot table. I did
something like this a while ago using a formula but cant seem to recall it
anymore. Any suggestions would be greatly appreciated. Thanks in advance.

[Jim May]

in Cell A6 enter Cat - White
in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)
HTH


"sumitk" wrote:

> Hi. I need to be able to do a summary report where I need to add all the
> numbers if the row and column headers both meet certain criteria. For
> example, in the table below, I would like to get a value for the combination
> Cat/White (i.e. 5+35 = 40) or Dog/Brown (i.e. 30)
>
> Row/Col. A B C D
> 1 Animal/Color White Black Brown
> 2 Cat 5 10 15
> 3 Dog 20 25 30
> 4 Cat 35 40 45
>
> In this particular situation, I do not want to use a pivot table. I did
> something like this a while ago using a formula but cant seem to recall it
> anymore. Any suggestions would be greatly appreciated. Thanks in advance.
>

[sumitk]

Thanks Jim. My apologies but I should have mentioned earlier that the order
of the values in the row & col. headings could be random.. in other words,
White could be in col. C vs B the next time I do a data dump from the source
system. Same applies to the Values in Col. A as well.

Regards,
Sumitk

"Jim May" wrote:

> in Cell A6 enter Cat - White
> in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)
> HTH
>
>
> "sumitk" wrote:
>
> > Hi. I need to be able to do a summary report where I need to add all the
> > numbers if the row and column headers both meet certain criteria. For
> > example, in the table below, I would like to get a value for the combination
> > Cat/White (i.e. 5+35 = 40) or Dog/Brown (i.e. 30)
> >
> > Row/Col. A B C D
> > 1 Animal/Color White Black Brown
> > 2 Cat 5 10 15
> > 3 Dog 20 25 30
> > 4 Cat 35 40 45
> >
> > In this particular situation, I do not want to use a pivot table. I did
> > something like this a while ago using a formula but cant seem to recall it
> > anymore. Any suggestions would be greatly appreciated. Thanks in advance.
> >

[sumitk]

Also, the only thing I remember using in the past is a "SumProduct" formula.

sk

"sumitk" wrote:

> Thanks Jim. My apologies but I should have mentioned earlier that the order
> of the values in the row & col. headings could be random.. in other words,
> White could be in col. C vs B the next time I do a data dump from the source
> system. Same applies to the Values in Col. A as well.
>
> Regards,
> Sumitk
>
> "Jim May" wrote:
>
> > in Cell A6 enter Cat - White
> > in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)
> > HTH
> >
> >
> > "sumitk" wrote:
> >
> > > Hi. I need to be able to do a summary report where I need to add all the
> > > numbers if the row and column headers both meet certain criteria. For
> > > example, in the table below, I would like to get a value for the combination
> > > Cat/White (i.e. 5+35 = 40) or Dog/Brown (i.e. 30)
> > >
> > > Row/Col. A B C D
> > > 1 Animal/Color White Black Brown
> > > 2 Cat 5 10 15
> > > 3 Dog 20 25 30
> > > 4 Cat 35 40 45
> > >
> > > In this particular situation, I do not want to use a pivot table. I did
> > > something like this a while ago using a formula but cant seem to recall it
> > > anymore. Any suggestions would be greatly appreciated. Thanks in advance.
> > >

[Jim May]

Off hand, I can't help you on this -- the Sumproduct is an array-type
formula which oftern(if not always) requires the same number of
rows for each element yu are querying.
Sorry, perhaps someone else can assist you.
Jim

"sumitk" wrote:

> Also, the only thing I remember using in the past is a "SumProduct" formula.
>
> sk
>
> "sumitk" wrote:
>
> > Thanks Jim. My apologies but I should have mentioned earlier that the order
> > of the values in the row & col. headings could be random.. in other words,
> > White could be in col. C vs B the next time I do a data dump from the source
> > system. Same applies to the Values in Col. A as well.
> >
> > Regards,
> > Sumitk
> >
> > "Jim May" wrote:
> >
> > > in Cell A6 enter Cat - White
> > > in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)
> > > HTH
> > >
> > >
> > > "sumitk" wrote:
> > >
> > > > Hi. I need to be able to do a summary report where I need to add all the
> > > > numbers if the row and column headers both meet certain criteria. For
> > > > example, in the table below, I would like to get a value for the combination
> > > > Cat/White (i.e. 5+35 = 40) or Dog/Brown (i.e. 30)
> > > >
> > > > Row/Col. A B C D
> > > > 1 Animal/Color White Black Brown
> > > > 2 Cat 5 10 15
> > > > 3 Dog 20 25 30
> > > > 4 Cat 35 40 45
> > > >
> > > > In this particular situation, I do not want to use a pivot table. I did
> > > > something like this a while ago using a formula but cant seem to recall it
> > > > anymore. Any suggestions would be greatly appreciated. Thanks in advance.
> > > >

[Jim May]

All I can come up with (in thinking through this) is that
you create yourself a standard Block (as Below) including
for example where the 65.00 is:

=SUMIF($A$2:$A$7,$A12,B$2:B$7)
and copy to other figures in the block.

It will accomodate what you need as long as the
Range is set right.



Summary White Black Brown
Cat 65.00 105.00 120.00
Dog 45.00 75.00 90.00
110.00 180.00 210.00


"Jim May" wrote:

> Off hand, I can't help you on this -- the Sumproduct is an array-type
> formula which oftern(if not always) requires the same number of
> rows for each element yu are querying.
> Sorry, perhaps someone else can assist you.
> Jim
>
> "sumitk" wrote:
>
> > Also, the only thing I remember using in the past is a "SumProduct" formula.
> >
> > sk
> >
> > "sumitk" wrote:
> >
> > > Thanks Jim. My apologies but I should have mentioned earlier that the order
> > > of the values in the row & col. headings could be random.. in other words,
> > > White could be in col. C vs B the next time I do a data dump from the source
> > > system. Same applies to the Values in Col. A as well.
> > >
> > > Regards,
> > > Sumitk
> > >
> > > "Jim May" wrote:
> > >
> > > > in Cell A6 enter Cat - White
> > > > in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)
> > > > HTH
> > > >
> > > >
> > > > "sumitk" wrote:
> > > >
> > > > > Hi. I need to be able to do a summary report where I need to add all the
> > > > > numbers if the row and column headers both meet certain criteria. For
> > > > > example, in the table below, I would like to get a value for the combination
> > > > > Cat/White (i.e. 5+35 = 40) or Dog/Brown (i.e. 30)
> > > > >
> > > > > Row/Col. A B C D
> > > > > 1 Animal/Color White Black Brown
> > > > > 2 Cat 5 10 15
> > > > > 3 Dog 20 25 30
> > > > > 4 Cat 35 40 45
> > > > >
> > > > > In this particular situation, I do not want to use a pivot table. I did
> > > > > something like this a while ago using a formula but cant seem to recall it
> > > > > anymore. Any suggestions would be greatly appreciated. Thanks in advance.
> > > > >

[PaulW]

> > in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)

> > > Row/Col. A B C D
> > > 1 Animal/Color White Black Brown
> > > 2 Cat 5 10 15
> > > 3 Dog 20 25 30
> > > 4 Cat 35 40 45

=sumif($A$2:A$4,"Cat",B2:B4)

Will work in the current table. If White is going to move, put something in
to search for "White", what I do is =match("White",1:1,0) which in this case
will return 2. I know its not ideal, but I then have a little table with a
Column as the numbers counting, and another as Letters running. Then do a
Vlookup of this Match, so that 2 becomes B.

Say this vlookup graph is in A50:B100, then =vlookup(2,A50:B100,2,FALSE)
Adding the match into this it becomes,
=vlookup(match("White",1:1,0),A50:B100,2,FALSE)
Concatenate will change this to
=concatenate(vlookup(match("White",1:1,0),A50:B100,2,FALSE),":",vlookup(match("White",1:1,0),A50:B100,2,FALSE))
Which in this case will return B:B

Using indirect to use this
sumif($A$2:A$4,"Cat",indirect(concatenate(vlookup(match("White",1:1,0),$A$50:$B$100,2,FALSE),":",vlookup(match("White",1:1,0),$A$50:$B$100,2,FALSE))))

If you wanted these results to be in say G1, you could put White in H1,
Black in I1 and Brown in J1, in G2 put "Cat" and in G3 put "Dog". Copy the
above formula into H1, and change the Cat and Whites to cell references, so
it ends up as

=sumif($A$2:A$4,$G2,indirect(concatenate(vlookup(match(H$1,1:1,0),$A$50:$B$100,2,FALSE),":",vlookup(match(H$1,1:1,0),$A$50:$B$100,2,FALSE))))

You can then drag this formula into the other cells for this summary.

Even if this isn't exactly what you want, hopefully it'll help you get there

[sumitk]

Thanks Paul & Jim. I think I finally have this working.

Regards,
Sumit

"PaulW" wrote:

> > > in B6 enter =sumif($A$2:$A$4,"Cat",B2:B4)
>
> > > > Row/Col. A B C D
> > > > 1 Animal/Color White Black Brown
> > > > 2 Cat 5 10 15
> > > > 3 Dog 20 25 30
> > > > 4 Cat 35 40 45
>
> =sumif($A$2:A$4,"Cat",B2:B4)
>
> Will work in the current table. If White is going to move, put something in
> to search for "White", what I do is =match("White",1:1,0) which in this case
> will return 2. I know its not ideal, but I then have a little table with a
> Column as the numbers counting, and another as Letters running. Then do a
> Vlookup of this Match, so that 2 becomes B.
>
> Say this vlookup graph is in A50:B100, then =vlookup(2,A50:B100,2,FALSE)
> Adding the match into this it becomes,
> =vlookup(match("White",1:1,0),A50:B100,2,FALSE)
> Concatenate will change this to
> =concatenate(vlookup(match("White",1:1,0),A50:B100,2,FALSE),":",vlookup(match("White",1:1,0),A50:B100,2,FALSE))
> Which in this case will return B:B
>
> Using indirect to use this
> sumif($A$2:A$4,"Cat",indirect(concatenate(vlookup(match("White",1:1,0),$A$50:$B$100,2,FALSE),":",vlookup(match("White",1:1,0),$A$50:$B$100,2,FALSE))))
>
> If you wanted these results to be in say G1, you could put White in H1,
> Black in I1 and Brown in J1, in G2 put "Cat" and in G3 put "Dog". Copy the
> above formula into H1, and change the Cat and Whites to cell references, so
> it ends up as
>
> =sumif($A$2:A$4,$G2,indirect(concatenate(vlookup(match(H$1,1:1,0),$A$50:$B$100,2,FALSE),":",vlookup(match(H$1,1:1,0),$A$50:$B$100,2,FALSE))))
>
> You can then drag this formula into the other cells for this summary.
>
> Even if this isn't exactly what you want, hopefully it'll help you get there
>