Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on. Any
suggestions would be greatly appreciated.

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson

Bob, The formula you suggested DOES work. Thank you very much. I figured out
what I was doing "wrong" but now have another problem...lol...

I changed the formula to be: =SUMPRODUCT(-(A:A=23),--(C:C="M"),B:B) because
we don't know how many rows there will be each month. After some trial and
error -- mostly error - I found is that if more rows are referenced in the
formula than are actually in the table, an error is produced. When I change
the formula to reference rows that are actually populated, it does work. Is
there a way to create this formula to look at all populated rows without
specifically referencing the row numbers in the formula?

Thanks again for your help with this!

"Elizabeth" wrote:

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.

Hi Dave, Thank you for your help with this. I was posting at the same you
were so our posts overlapped. Your formula fix did work, the original formula
did work, it just produced a negative number.

My problem was that I had changed the formula to:
=SUMPRODUCT(--(A:A=23),--(C:C="M"),B:B)
because we never know how many rows the data will fill. This is when I get
an error (found this out after many trials...) If I reference the exact
number of rows - or fewer - that are populated, the formula works perfectly!
If more rows are referenced in the forumla than are populated, I get the
NUMBER error. Any suggestions on how to "fix" this would be great! Thanks
again.
Elizabeth


"Dave Peterson" wrote:

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson

You can't use the whole column with these kind of formulas.

But you can use lots of them--if you put headers in row 1:

=SUMPRODUCT(--(A2:A65536=23),--(C2:C65536="M"),B2:B65536)

I'd just use a number that I know wouldn't be exceeded. If you expect 1000
entries, make it 5000.

You know--just double it and add a bit more so you can sleep nights!

Elizabeth wrote:

Hi Dave, Thank you for your help with this. I was posting at the same you
were so our posts overlapped. Your formula fix did work, the original formula
did work, it just produced a negative number.

My problem was that I had changed the formula to:
=SUMPRODUCT(--(A:A=23),--(C:C="M"),B:B)
because we never know how many rows the data will fill. This is when I get
an error (found this out after many trials...) If I reference the exact
number of rows - or fewer - that are populated, the formula works perfectly!
If more rows are referenced in the forumla than are populated, I get the
NUMBER error. Any suggestions on how to "fix" this would be great! Thanks
again.
Elizabeth

"Dave Peterson" wrote:

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson


--

Dave Peterson

Hi Dave,

It worked! Thank you and Bob Phillips SO MUCH! It's always the "little"
things that matter the most, like needing first the correct formula, then a
header then the need to specify rows (even if they are alot...lol...) but it
worked. I sincerely appreciate both of your help. Within just a short while,
you both resolved a problem that we have had for...let's just say its been a
long time...lol...thanks again.
Elizabeth

"Dave Peterson" wrote:

You can't use the whole column with these kind of formulas.

But you can use lots of them--if you put headers in row 1:

=SUMPRODUCT(--(A2:A65536=23),--(C2:C65536="M"),B2:B65536)

I'd just use a number that I know wouldn't be exceeded. If you expect 1000
entries, make it 5000.

You know--just double it and add a bit more so you can sleep nights!

Elizabeth wrote:

Hi Dave, Thank you for your help with this. I was posting at the same you
were so our posts overlapped. Your formula fix did work, the original formula
did work, it just produced a negative number.

My problem was that I had changed the formula to:
=SUMPRODUCT(--(A:A=23),--(C:C="M"),B:B)
because we never know how many rows the data will fill. This is when I get
an error (found this out after many trials...) If I reference the exact
number of rows - or fewer - that are populated, the formula works perfectly!
If more rows are referenced in the forumla than are populated, I get the
NUMBER error. Any suggestions on how to "fix" this would be great! Thanks
again.
Elizabeth

"Dave Peterson" wrote:

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson


--

Dave Peterson

Glad it worked. (And I'm sure Bob appreciates the thanks, too.)

Elizabeth wrote:

Hi Dave,

It worked! Thank you and Bob Phillips SO MUCH! It's always the "little"
things that matter the most, like needing first the correct formula, then a
header then the need to specify rows (even if they are alot...lol...) but it
worked. I sincerely appreciate both of your help. Within just a short while,
you both resolved a problem that we have had for...let's just say its been a
long time...lol...thanks again.
Elizabeth

"Dave Peterson" wrote:

You can't use the whole column with these kind of formulas.

But you can use lots of them--if you put headers in row 1:

=SUMPRODUCT(--(A2:A65536=23),--(C2:C65536="M"),B2:B65536)

I'd just use a number that I know wouldn't be exceeded. If you expect 1000
entries, make it 5000.

You know--just double it and add a bit more so you can sleep nights!

Elizabeth wrote:

Hi Dave, Thank you for your help with this. I was posting at the same you
were so our posts overlapped. Your formula fix did work, the original formula
did work, it just produced a negative number.

My problem was that I had changed the formula to:
=SUMPRODUCT(--(A:A=23),--(C:C="M"),B:B)
because we never know how many rows the data will fill. This is when I get
an error (found this out after many trials...) If I reference the exact
number of rows - or fewer - that are populated, the formula works perfectly!
If more rows are referenced in the forumla than are populated, I get the
NUMBER error. Any suggestions on how to "fix" this would be great! Thanks
again.
Elizabeth

"Dave Peterson" wrote:

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula you suggested
gives me a #Value! error. When I step through it, it shows an immediate false
during the attempt to find the 23, that I know is there... I've tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT, ENTER), changing
the A column to text, back to a number...no luck with any of these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in message
...
Hi, I am trying to create a formula that adds certain values from Column B
only if Column A equals a certain criteria AND Column C equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different formulas but
they either total the entire B Column (not just the cells that match the
criteria) or they only "sum" Column B for the row the formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Didn't feel I deserved them Dave!

Bob

"Dave Peterson" wrote in message
...
Glad it worked. (And I'm sure Bob appreciates the thanks, too.)

Elizabeth wrote:

Hi Dave,

It worked! Thank you and Bob Phillips SO MUCH! It's always the "little"
things that matter the most, like needing first the correct formula,
then a
header then the need to specify rows (even if they are alot...lol...)
but it
worked. I sincerely appreciate both of your help. Within just a short
while,
you both resolved a problem that we have had for...let's just say its
been a
long time...lol...thanks again.
Elizabeth

"Dave Peterson" wrote:

You can't use the whole column with these kind of formulas.

But you can use lots of them--if you put headers in row 1:

=SUMPRODUCT(--(A2:A65536=23),--(C2:C65536="M"),B2:B65536)

I'd just use a number that I know wouldn't be exceeded. If you expect
1000
entries, make it 5000.

You know--just double it and add a bit more so you can sleep nights!

Elizabeth wrote:

Hi Dave, Thank you for your help with this. I was posting at the
same you
were so our posts overlapped. Your formula fix did work, the
original formula
did work, it just produced a negative number.

My problem was that I had changed the formula to:
=SUMPRODUCT(--(A:A=23),--(C:C="M"),B:B)
because we never know how many rows the data will fill. This is when
I get
an error (found this out after many trials...) If I reference the
exact
number of rows - or fewer - that are populated, the formula works
perfectly!
If more rows are referenced in the forumla than are populated, I get
the
NUMBER error. Any suggestions on how to "fix" this would be great!
Thanks
again.
Elizabeth

"Dave Peterson" wrote:

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula
you suggested
gives me a #Value! error. When I step through it, it shows an
immediate false
during the attempt to find the 23, that I know is there... I've
tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT,
ENTER), changing
the A column to text, back to a number...no luck with any of
these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in
message
...
Hi, I am trying to create a formula that adds certain values
from Column B
only if Column A equals a certain criteria AND Column C
equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on
the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different
formulas but
they either total the entire B Column (not just the cells
that match the
criteria) or they only "sum" Column B for the row the
formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You gave her the original formula that worked ok!

I'm getting used to speaking for others, so I'm sure Elizabeth wants you to know
(again!) how much she appreciates it.



Bob Phillips wrote:

Didn't feel I deserved them Dave!

Bob

"Dave Peterson" wrote in message
...
Glad it worked. (And I'm sure Bob appreciates the thanks, too.)

Elizabeth wrote:

Hi Dave,

It worked! Thank you and Bob Phillips SO MUCH! It's always the "little"
things that matter the most, like needing first the correct formula,
then a
header then the need to specify rows (even if they are alot...lol...)
but it
worked. I sincerely appreciate both of your help. Within just a short
while,
you both resolved a problem that we have had for...let's just say its
been a
long time...lol...thanks again.
Elizabeth

"Dave Peterson" wrote:

You can't use the whole column with these kind of formulas.

But you can use lots of them--if you put headers in row 1:

=SUMPRODUCT(--(A2:A65536=23),--(C2:C65536="M"),B2:B65536)

I'd just use a number that I know wouldn't be exceeded. If you expect
1000
entries, make it 5000.

You know--just double it and add a bit more so you can sleep nights!

Elizabeth wrote:

Hi Dave, Thank you for your help with this. I was posting at the
same you
were so our posts overlapped. Your formula fix did work, the
original formula
did work, it just produced a negative number.

My problem was that I had changed the formula to:
=SUMPRODUCT(--(A:A=23),--(C:C="M"),B:B)
because we never know how many rows the data will fill. This is when
I get
an error (found this out after many trials...) If I reference the
exact
number of rows - or fewer - that are populated, the formula works
perfectly!
If more rows are referenced in the forumla than are populated, I get
the
NUMBER error. Any suggestions on how to "fix" this would be great!
Thanks
again.
Elizabeth

"Dave Peterson" wrote:

Do you have any errors in A2:C20 (any cell will cause trouble)?

And I think Bob meant:

=SUMPRODUCT(--(A2:A20=23),--(C2:C20="M"),B2:B20)
(two negative signs in the first portion)

and you don't need to use ctrl-shift-enter for this formula

Elizabeth wrote:

Hi Bob, Thanks so much for your help and your time. The formula
you suggested
gives me a #Value! error. When I step through it, it shows an
immediate false
during the attempt to find the 23, that I know is there... I've
tried putting
quotes around the 23, saving is as an array (CTRL, SHIFT,
ENTER), changing
the A column to text, back to a number...no luck with any of
these attempts.

"Bob Phillips" wrote:

=SUMPRODUCT(-(A2:A20=23),--(C2:C20="M"),B2:B20)

etc.

--
HTH

Bob Phillips

"Elizabeth" wrote in
message
...
Hi, I am trying to create a formula that adds certain values
from Column B
only if Column A equals a certain criteria AND Column C
equals a certain
criteria.

Column A Column B Column C

23 23.88 V
23 100.00 M
24 9.95 M
24 23.88 M
25 23.88 V
25 23.88 V
26 23.88 A
26 23.88 A
26 23.88 M
26 23.88 M
26 23.88 V
26 9.95 V
27 23.88 A

Specifically, I need the total from Column B for all M's on
the 23rd, all
V's on the 23rd, etc. In Column D, I've tried many different
formulas but
they either total the entire B Column (not just the cells
that match the
criteria) or they only "sum" Column B for the row the
formula resides on.
Any
suggestions would be greatly appreciated.




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson