Wildcard * for SUMIFS
 

I am trying to do a SUMIFS calculation and the wildcards are not working.
Here is the formula:

=SUMIFS('Rents'!U:U,'Rents'!Y:Y,"y",'Rents'!P:P,"* ",'Rents'!O:O,"*")

Where 'Rents'!O:O is a dollar figure. I need it to include data from the
sum range only if there is an entry (any entry) for the rent figure from
Column O.

I am testing it on one row of data, with "10" entered in Column O. When I
use "*" it does not count the data in sum range, if I enter the exact data
("10") it does. Thus the wildcard is not working to return a TRUE for the
data in question.

How do I get this to work? Thanks for your help.

Wildcard * for SUMIFS
 

Wildcards don't work on numbers, only TEXT.

Maybe this:

=SUMIFS('Rents'!U:U,'Rents'!Y:Y,"y",'Rents'!P:P,"* ",'Rents'!O:O,"0")

You didn't say what's in P:P. If it's numeric the "*" won't work.


--
Biff
Microsoft Excel MVP


"Steve Kasher" wrote in message
...
I am trying to do a SUMIFS calculation and the wildcards are not working.
Here is the formula:

=SUMIFS('Rents'!U:U,'Rents'!Y:Y,"y",'Rents'!P:P,"* ",'Rents'!O:O,"*")

Where 'Rents'!O:O is a dollar figure. I need it to include data from the
sum range only if there is an entry (any entry) for the rent figure from
Column O.

I am testing it on one row of data, with "10" entered in Column O. When I
use "*" it does not count the data in sum range, if I enter the exact data
("10") it does. Thus the wildcard is not working to return a TRUE for the
data in question.

How do I get this to work? Thanks for your help.

Wildcard * for SUMIFS
 

If you're summing column 0, what difference does it make if you include or
exclude cells that are empty or 0's?

0+anynumber (and an empty cell will be treated as 0) is just that anynumber.

Steve Kasher wrote:

I am trying to do a SUMIFS calculation and the wildcards are not working.
Here is the formula:

=SUMIFS('Rents'!U:U,'Rents'!Y:Y,"y",'Rents'!P:P,"* ",'Rents'!O:O,"*")

Where 'Rents'!O:O is a dollar figure. I need it to include data from the
sum range only if there is an entry (any entry) for the rent figure from
Column O.

I am testing it on one row of data, with "10" entered in Column O. When I
use "*" it does not count the data in sum range, if I enter the exact data
("10") it does. Thus the wildcard is not working to return a TRUE for the
data in question.

How do I get this to work? Thanks for your help.

--

Dave Peterson