Nesting SUMIF with Wildcard
 

Can someone please tell what I am doing wrong with the following sumif
statement? When I try each piece separatly I get a number but when I put
them together Excel says something is wrong.

sumif(QueryFromAccessRedo!E:E,"=#N/A",sumif(QueryFromAccessRedo!B:B,"*NR",QueryFromAc cessRedo!C:C))

Or if you could help me use SUMPRODUCT - I just can't figure it out there
either.

Any help would be greatly appreciated! Thanks!
Shanna

Nesting SUMIF with Wildcard
 

=SUMPRODUCT(--(ISNA(QueryFromAccessRedo!E1:E1000),--(ISNUMBER(FIND("NR",QueryFromAccessRedo!B1:B1000)) ),QueryFromAccessRedo!C1:C1000))

SUMPRODUCT does not accept: Full column references as in B:A, or wild card
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"ShannaD" wrote in message
...
Can someone please tell what I am doing wrong with the following sumif
statement? When I try each piece separatly I get a number but when I put
them together Excel says something is wrong.

sumif(QueryFromAccessRedo!E:E,"=#N/A",sumif(QueryFromAccessRedo!B:B,"*NR",QueryFromAc cessRedo!C:C))

Or if you could help me use SUMPRODUCT - I just can't figure it out there
either.

Any help would be greatly appreciated! Thanks!
Shanna

Nesting SUMIF with Wildcard
 

Bernard,

I really appreciate the help and I definitely didn't know that. However,
when I took your equation and put it into my document, Excel still will not
process it. It is giving me the dialog box that says the equation contains
an error, which was the same thing it was giving me earlier.

Do you have any ideas where this error could be?

Thanks,
Shanna

"Bernard Liengme" wrote:

=SUMPRODUCT(--(ISNA(QueryFromAccessRedo!E1:E1000),--(ISNUMBER(FIND("NR",QueryFromAccessRedo!B1:B1000)) ),QueryFromAccessRedo!C1:C1000))

SUMPRODUCT does not accept: Full column references as in B:A, or wild card
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"ShannaD" wrote in message
...
Can someone please tell what I am doing wrong with the following sumif
statement? When I try each piece separatly I get a number but when I put
them together Excel says something is wrong.

sumif(QueryFromAccessRedo!E:E,"=#N/A",sumif(QueryFromAccessRedo!B:B,"*NR",QueryFromAc cessRedo!C:C))

Or if you could help me use SUMPRODUCT - I just can't figure it out there
either.

Any help would be greatly appreciated! Thanks!
Shanna

Nesting SUMIF with Wildcard
 

Mismatched parenthesis ( )

Try this:

=SUMPRODUCT(--(ISNA(QueryFromAccessRedo!E1:E1000)),--(ISNUMBER(FIND("NR",QueryFromAccessRedo!B1:B1000)) ),QueryFromAccessRedo!C1:C1000)

Note that FIND is case sensitive.

"NR" and "nr" will not match.

If you don't want to test for specific case replace FIND with SEARCH.


--
Biff
Microsoft Excel MVP


"ShannaD" wrote in message
...
Bernard,

I really appreciate the help and I definitely didn't know that. However,
when I took your equation and put it into my document, Excel still will
not
process it. It is giving me the dialog box that says the equation
contains
an error, which was the same thing it was giving me earlier.

Do you have any ideas where this error could be?

Thanks,
Shanna

"Bernard Liengme" wrote:

=SUMPRODUCT(--(ISNA(QueryFromAccessRedo!E1:E1000),--(ISNUMBER(FIND("NR",QueryFromAccessRedo!B1:B1000)) ),QueryFromAccessRedo!C1:C1000))

SUMPRODUCT does not accept: Full column references as in B:A, or wild
card
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"ShannaD" wrote in message
...
Can someone please tell what I am doing wrong with the following sumif
statement? When I try each piece separatly I get a number but when I
put
them together Excel says something is wrong.

sumif(QueryFromAccessRedo!E:E,"=#N/A",sumif(QueryFromAccessRedo!B:B,"*NR",QueryFromAc cessRedo!C:C))

Or if you could help me use SUMPRODUCT - I just can't figure it out
there
either.

Any help would be greatly appreciated! Thanks!
Shanna