Counting Cells based on 2 different criteria

[camelrunner]

I need to count the number of non-blank cells in an array that do not contain the text "Neg". The cells in the array are merged and each cell contains a formula, so while they are blank (they have no visible value) they still contain a formula. The formula I have so far =SUMIFS(B18:M25,B18:M25,SUM(--(TRIM(B18:M25)<>"")),B18:M25,"<>*Neg*"), gives me 0 when it should be 2 based on some testing.

 

[Joe4]

Which cells are merged?

Can you show us some sample data and your expected result?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Fluff]

Maybe

=COUNTIFS(B18:M25,"<>",B18:M25,"<>*Neg*")

 

[camelrunner]

Maybe

=COUNTIFS(B18:M25,"<>",B18:M25,"<>*Neg*")

This counts the cells that are blank as well. It works for not counting the Negs, but the blanks are still counted. The blank cells contain a formula that will appear empty if there is no input for that cell, so excel sees the blanks as containing a value due to the formula

 

[Joe4]

This counts the cells that are blank as well. It works for not counting the Negs, but the blanks are still counted. The blank cells contain a formula that will appear empty if there is no input for that cell, so excel sees the blanks as containing a value due to the formula

Can you post what these formulas look like exactly, so we can see what they might be returning?
These details are important, which is why I asked to see a sampling of your data.
All blanks are not created equal (and a "space" is NOT the same as a blank).

 

[LogChief]

Try this formula:

=SUM((B18:M25 > 0) * ISNUMBER(FIND("Neg", B18:M25)))