Nesting SUMIF with Wildcard

[ShannaD]

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",QueryFromAccessRedo!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

 

[Bernard Liengme]

=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

 

[ShannaD]

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

 

[T. Valko]

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.