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

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

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.