I want to create a subtotal for all items with a status of "not cleared" when
the region is East.

My columns are set up so the Region column is A, my total value column is Q
and my status column is U

The team has helped me a great deal providing me with this formula:

=SUMIF(U3:U3000,"<cleared",Q3:Q3000)
which subtotals cleared items, but now that i have created a summary tab, I
wanted to include the Region variable.

How do I add another variable to this equation?

Thanks!!!

Hi, try:

=Sumproduct(--(A3:A3000="East")*(U3:U3000="not cleared")*(Q3:Q3000))

Regards - Dave


"Belinda7237" wrote:

I want to create a subtotal for all items with a status of "not cleared" when
the region is East.

My columns are set up so the Region column is A, my total value column is Q
and my status column is U

The team has helped me a great deal providing me with this formula:

=SUMIF(U3:U3000,"<cleared",Q3:Q3000)
which subtotals cleared items, but now that i have created a summary tab, I
wanted to include the Region variable.

How do I add another variable to this equation?

Thanks!!!

Its returning a #N/A ?

"Dave" wrote:

Hi, try:

=Sumproduct(--(A3:A3000="East")*(U3:U3000="not cleared")*(Q3:Q3000))

Regards - Dave


"Belinda7237" wrote:

I want to create a subtotal for all items with a status of "not cleared" when
the region is East.

My columns are set up so the Region column is A, my total value column is Q
and my status column is U

The team has helped me a great deal providing me with this formula:

=SUMIF(U3:U3000,"<cleared",Q3:Q3000)
which subtotals cleared items, but now that i have created a summary tab, I
wanted to include the Region variable.

How do I add another variable to this equation?

Thanks!!!

I noticed I had a couple of fields that had #N/A so I have removed them - but
now the answer is 0 and it should be a large number...getting closer?

"Belinda7237" wrote:

I want to create a subtotal for all items with a status of "not cleared" when
the region is East.

My columns are set up so the Region column is A, my total value column is Q
and my status column is U

The team has helped me a great deal providing me with this formula:

=SUMIF(U3:U3000,"<cleared",Q3:Q3000)
which subtotals cleared items, but now that i have created a summary tab, I
wanted to include the Region variable.

How do I add another variable to this equation?

Thanks!!!

Hi,
Not sure why. I don't have your data, so I can't test it.
If you're up to it, try this:

To check where it's going wrong, reduce each range to 30. ie:

=Sumproduct(--(A3:A30="East")*(U3:U30="not cleared")*(Q3:Q30))

Select the cell you have your formula in. Then:
In the formula bar, highlight just: A3:A30="East"
Press F9
It should give a list of 27 TRUE's or FALSE's
Press escape. (Don't forget this part)
Do the same for: U3:U30="not cleared"
which should give a similar list.
Do the same for Q3:Q30
which should give a list of values.
If any of them return an error, that's where the fault is.
Let me know.
Regards - Dave.

Hi,
Do you actually have "not cleared" (without the quotes) in some cells in
Column U?
Has Column A got clean data? ie do the "East" entries have trailing or
leading spaces?
Dave.

"Belinda7237" wrote:

I noticed I had a couple of fields that had #N/A so I have removed them - but
now the answer is 0 and it should be a large number...getting closer?

"Belinda7237" wrote:

I want to create a subtotal for all items with a status of "not cleared" when
the region is East.

My columns are set up so the Region column is A, my total value column is Q
and my status column is U

The team has helped me a great deal providing me with this formula:

=SUMIF(U3:U3000,"<cleared",Q3:Q3000)
which subtotals cleared items, but now that i have created a summary tab, I
wanted to include the Region variable.

How do I add another variable to this equation?

Thanks!!!

On Jun 18, 5:09 pm, "Don Guillett" wrote:
try
=Sumproduct((A3:A3000="East")*(U3:U3000<"cleared" )*(Q3:Q3000))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Dave" wrote in message

...

Hi, try:

=Sumproduct(--(A3:A3000="East")*(U3:U3000="not cleared")*(Q3:Q3000))

Regards - Dave


I thought it would be the following to actually sum the Q column:
=Sumproduct(--(A3:A3000="East")*(U3:U3000<"cleared"),(Q3:Q3000) )

In column U i have cleared or its blank
so i replaced your not cleared with <cleared in the formula

i relooked at column A and I did have a couple of #N/A items therefore i
deleted them and cleaned that column up and reran and get a value of 0 in my
total.

"Dave" wrote:

Hi,
Do you actually have "not cleared" (without the quotes) in some cells in
Column U?
Has Column A got clean data? ie do the "East" entries have trailing or
leading spaces?
Dave.

"Belinda7237" wrote:

I noticed I had a couple of fields that had #N/A so I have removed them - but
now the answer is 0 and it should be a large number...getting closer?

"Belinda7237" wrote:

I want to create a subtotal for all items with a status of "not cleared" when
the region is East.

My columns are set up so the Region column is A, my total value column is Q
and my status column is U

The team has helped me a great deal providing me with this formula:

=SUMIF(U3:U3000,"<cleared",Q3:Q3000)
which subtotals cleared items, but now that i have created a summary tab, I
wanted to include the Region variable.

How do I add another variable to this equation?

Thanks!!!

On Jun 24, 10:05 am, Belinda7237
wrote:
In column U i have cleared or its blank
so i replaced your not cleared with <cleared in the formula

Which did you put in:

<cleared
<"cleared"

You need the quotes to identify text. Seems to work for me with a
quick test.

I used "<cleared" see my updated formula below:

=SUMPRODUCT(--('East Master Repository'!A3:A3000="EAST")*('East Master
Repository'!U3:U3000="<cleared")*('East Master Repository'!Q3:Q3000))

"Spiky" wrote:

On Jun 24, 10:05 am, Belinda7237
wrote:
In column U i have cleared or its blank
so i replaced your not cleared with <cleared in the formula

Which did you put in:

<cleared
<"cleared"

You need the quotes to identify text. Seems to work for me with a
quick test.

thanks, based on your comments i put the <"cleared" and it worked!

Appreciate your help!!

"Spiky" wrote:

On Jun 24, 10:05 am, Belinda7237
wrote:
In column U i have cleared or its blank
so i replaced your not cleared with <cleared in the formula

Which did you put in:

<cleared
<"cleared"

You need the quotes to identify text. Seems to work for me with a
quick test.

On Jun 24, 2:16 pm, Belinda7237
wrote:
thanks, based on your comments i put the <"cleared" and it worked!

Appreciate your help!!

"Spiky" wrote:
On Jun 24, 10:05 am, Belinda7237
wrote:
In column U i have cleared or its blank
so i replaced your not cleared with <cleared in the formula

Which did you put in:

<cleared
<"cleared"

You need the quotes to identify text. Seems to work for me with a
quick test.

Excellent! Glad we finally got there. Sometimes it takes many eyes to
troubleshoot.