software

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 CD
1Animal/Color White BlackBrown
2Cat 5 1015
3Dog 20 2530
4Cat 35 4045

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.in Cell A6 enter Cat - White
in B6 enter =sumif($A$2:$A$4,quot;Catquot;,B2:B4)
HTHquot;sumitkquot; wrote:

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

Thanks Jim. My apologies but I should have mentioned earlier that the order
of the values in the row amp; 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

quot;Jim Mayquot; wrote:

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

Also, the only thing I remember using in the past is a quot;SumProductquot; formula.

sk

quot;sumitkquot; wrote:

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

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

quot;sumitkquot; wrote:

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

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.00quot;Jim Mayquot; wrote:

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

gt; gt; in B6 enter =sumif($A$2:$A$4,quot;Catquot;,B2:B4)

gt; gt; gt; Row/Col.A B CD
gt; gt; gt; 1Animal/Color White BlackBrown
gt; gt; gt; 2Cat 5 1015
gt; gt; gt; 3Dog 20 2530
gt; gt; gt; 4Cat 35 4045

=sumif($A$2:A$4,quot;Catquot;,B2:B4)

Will work in the current table. If White is going to move, put something in
to search for quot;Whitequot;, what I do is =match(quot;Whitequot;,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(quot;Whitequot;,1:1,0),A50:B100,2,FALSE)
Concatenate will change this to
=concatenate(vlookup(match(quot;Whitequot;,1:1,0),A50:B100 ,2,FALSE),quot;:quot;,vlookup(match(quot;Whitequot;,1:1,0),A50:B10 0,2,FALSE))
Which in this case will return B:B

Using indirect to use this
sumif($A$2:A$4,quot;Catquot;,indirect(concatenate(vlookup( match(quot;Whitequot;,1:1,0),$A$50:$B$100,2,FALSE),quot;:quot;,vlo okup(match(quot;Whitequot;,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 quot;Catquot; and in G3 put quot;Dogquot;. 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(m atch(H$1,1:1,0),$A$50:$B$100,2,FALSE),quot;:quot;,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 thereThanks Paul amp; Jim. I think I finally have this working.

Regards,
Sumit

quot;PaulWquot; wrote:

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