!HELP! how to count the number of records excluding blank cells

**eksel_101** · 11-21-2012, 11:01 PM

I have this excel report,
Sheet1 contains Column State with values {size1,n/a,size3,size1,size3,size2,"","","",}
Sheet2 contains Column Total which should count size3 occurrences from Sheet1.

I have the following formula:
=COUNTA(SIRs_APT!A4:A302)

but i wanted to exlcude "n/a" occurrence from the count.

So i did this formula:
=COUNTIF(SIRs_AT!B2:B298,"<>*Rejected*")

but the problem is the current rows with data is only up to 10 rows. Hence, the count above includes the black rows which satisfied <> rejected condition. I wanted to retain the blank rows for future purposes.

Please help.

**FDibbins** · 11-21-2012, 11:07 PM

you're explanation is confusing (to me anyway). perhaps you you could countif() based on <>""

If that doesnt work, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**benishiryo** · 11-21-2012, 11:20 PM

hi eksel, welcome to the forum. if you are indeed using Excel 2010, try:
=COUNTIFS(SIRs_APT!B2:B298,"<>*Rejected*",SIRs_APT!B2:B298,"<>")

**eksel_101** · 11-21-2012, 11:28 PM

Originally Posted by FDibbins

you're explanation is confusing (to me anyway). perhaps you you could countif() based on <>""

If that doesnt work, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

hehehe..i re-read my post.. got myself confused as well..hahaha I'll try to upload to provide one sample workbook.

thanks Fdibbins