sumif with restrictions

[Guest]

I'm trying to create a formula that says "if column a=81500 AND if column
b=1885, then do a sumif(columnC,"<529999",">539999",columnD).

So, basically, I want to do a sumif function but put restrictions on it and
only add up those numbers in column D that meet the criteria.

To make things more difficult, I'd like the sumif funtion to lookup a range
of numbers (ie: 530000 thru 539999) vs. a single number in column C and
return the total of everything that matches that range from column D.

Any suggestions?

 

[Ron Coderre]

Try this:

=SUMPRODUCT((A1:A1000=81500)*(B1:B1000=1885)*(C1:C1000>529999)*(C1:C1000<539999)*(D11000)

Adjust range references to suit your situation (SUMPRODUCT won't work on
whole columns)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Read this requirement a little different from Ron:

.. sumif(columnC,"<529999",">539999",columnD)

Think you could try something like this:
=SUMPRODUCT((A1:A10=81500)*(B1:B10=1885)*((C1:C10<529999)+(C1:C10>539999))*D110)

Adapt the ranges to suit.

---

 

[Guest]

This is perfect! Thank you SO much!!!

Try this:

=SUMPRODUCT((A1:A1000=81500)*(B1:B1000=1885)*(C1:C1000>529999)*(C1:C1000<539999)*(D11000)

Adjust range references to suit your situation (SUMPRODUCT won't work on
whole columns)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron Coderre]

I'm glad I could help....Thanks for letting me know.

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Max]

.. then do a sumif(columnC,"<529999",">539999",columnD)

was wondering what you meant by your pseudo formula above? it was a bit
misleading to put it that way

---

 

[Guest]

Hey Max,

First off, thank you for responding. I really do appreciate your time and
input.

Secondly, my overall intention with this formula was to look at each row and
if the criteria matched, to add up the dollar amount in that row.

For example, Column "A" is a business unit, Column "B" is a sub code, and
Column "C" is a project code. The dollar amounts reside in Column "D".

My first intent was to say, if my criteria matches columns "A", "B", AND
"C", then sum up the dollar amounts in "D".

On top of that, I also wanted to be able to search "C" for a range of
project codes (ie: all project codes between 529999 and 539999) instead of
just one code.

That's what I tried to convey with the below formula, but obviously wasn't
super successful. I'll try to be extra clear with any future requests.

Again, thank you!

 

[Max]

First, thanks for posting back

Think I understood fairly well all of the multi-conditions that you
originally expressed <g>

The only one which proved confusing was this

your original post as expressed:

.. sumif(columnC,"<529999",">539999",columnD)

hints strongly at the "opposite" of your clarification:

.. (ie: all project codes between 529999 and 539999)

If ever you really need it as:

.. sumif(columnC,"<529999",">539999",columnD)

ie considering:
all project codes less than 529999, and
all project codes greater than 539999
(the 2 "outer" bounds)

then my earlier suggestion would do just that

---