sumproduct formula

[mojo2014]

=SUMPRODUCT(--($J$1:$J$380=$K393),--($O$1:$O$380<>""))

That is my formula is there a way for this formula to omit the #N/A? I have an "X" in the cells that i need counted but because of the #N/A it return the #N/A instead of the total of the X's

Sorry if this is confusing

[Tony Valko]

Try it like this...

=COUNTIFS($J$1:$J$380,$K393,$O$1:$O$380,"<>")

[Marcol]

Not to sure about that Tony.

If there are #N/A errors, in either range, that will return a potentially false count.

For example if a TRUE condition in the first range matches a #N/A in the second it will be counted.
This may or not be what the OP wants.

This disasterous CSE array formula will ignore all #N/A errors, by treating them as blanks.

Please Login or Register  to view this content.

Better the errors are fixed at source.

[mojo2014]

no i am getting a
#VALUE!

[Marcol]

Did you confirm the CSE formula I gave you with Ctrl+Shift+Enter not just Enter?

If that fails.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mojo2014]

2013 Master File1_TEST_FILE.xls

Basically All i am trying to do is get a count for example

B298 Has SERV in it so, i want to see how many "X" are there which will tell me how many people will be having Golf & Dinner. So I dont want it to count anything else but the "X"

Same would Apply to the D298, but once i see an example of C298 i am sure i will be able to figure out the rest

[Ace_XL]

In C298

=COUNTIFS(C$2:C$290,"X",$A$2:$A$290,$B298)

Copied dwon

[Marcol]

See if this helps
In C298 this array formula, confirm with Ctrl+Shift+Enter not just Enter. Then Drag across and down as required.

Please Login or Register  to view this content.

It would be so much easier and better to solve the #N/A errors at source

e.g.

Please Login or Register  to view this content.

[Marcol]

In C298

=COUNTIFS(C$2:C$290,"X",$A$2:$A$290,$B298)

Copied dwon

The file submitted is a 2003 file so you can't use COUNTIFS()

But, now I see, the OPs profile is 2010, so who knows ...

[Tony Valko]

=SUMPRODUCT(--($J$1:$J$380=$K393),--($O$1:$O$380<>""))

I interpreted the OPs description as meaning the #N/A errors are in column J. Based on that I did not consider that column O might have the errors.

So the COUNTIFS formula I suggested will ignore the errors in column J.

[Tony Valko]

Unfortunately, the attached sample file is too big so I can't see what's needed to solve this.

Sorry, but I won't download a file that's >50kb.

[Marcol]

It's an not unfrequent scenario Biff, as I'm sure you will be well aware.

The OPs' original post bore little or no resemblence to the actual problem he/she is faced with ...

I will repeat again

Better the errors are fixed at source.

xxx or should that be

Please Login or Register  to view this content.

[mojo2014]

See if this helps
In C298 this array formula, confirm with Ctrl+Shift+Enter not just Enter. Then Drag across and down as required.

Please Login or Register  to view this content.

It would be so much easier and better to solve the #N/A errors at source

e.g.

Please Login or Register  to view this content.

Awesome thank you so much

[mojo2014]

Why does this formula in C298 work on this spreadsheet but when i try using it but just changing the column id's and numbers it comes back as #value!