Formula Question SUMIF

[Belinda7237]

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!!!

 

[Dave]

Hi, try:

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

Regards - Dave

 

[Belinda7237]

Its returning a #N/A ?

Hi, try:

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

Regards - Dave

 

[Belinda7237]

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?

 

[Dave]

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.

 

[Don Guillett]

try
=Sumproduct((A3:A3000="East")*(U3:U3000<>"cleared")*(Q3:Q3000))

 

[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.

 

[Spiky]

try
=Sumproduct((A3:A3000="East")*(U3:U3000<>"cleared")*(Q3:Q3000))

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

 

[Belinda7237]

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.

 

[Spiky]

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.

 

[Belinda7237]

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))

 

[Belinda7237]

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

Appreciate your help!!

 

[Spiky]

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

Appreciate your help!!

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