Count/Sum Formula for large audit (150 tabs)

Hello all. I am stumped so came back to the boards for the first time in roughly 2 years, as I know someone will know. I am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected. Any ideas???

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

http://www.excelforum.com/excel-gene...need-help.html

Thread Closed.

I will re-open this post, the other 1 was closed. Please do not post duplicate threads, and please ammend your thread title

Thanks

Having 150 tabs sounds a bit crazy lol. Are the layouts the same on each? could you provide a *small* sample of what you are working with (no sensitive info), showing what you have, what you want and how you got that?

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

sorry for the delay

I am working on re-arranging your table completely, so that you have a table for your data, and then maybe another 1 that will extract as you want. If you want me to continue, I will, else if you thing that is not something that would work for you, let me know.

See the attached for what I have so far (sheet4, its a work in progress)...

Sorry, I uploaded the wrong file, I mistook this for another 1 i was working on

If you add this to E18 in each sheet, copied down...
=IF(C18="Yes",1,0)
and this to F18 in each sheet, copied down...
=IF(C18="N/A",1,0)

Then in sheet1 you can use this...
=SUM('(1): (3)'!E18:F18)

This will give you a total of all Yes and N/A. You can hide the helpers if you want, and an easy way to insert the formulas into all the data sheets is to use group mode.

click the 1st sheet, hold the shift and then click the last sheet. Now, whatever you do on 1 sheet, will be done to all of them. So type the 1st formula in, then copy it down. repeat for the 2nd formula and you'r done. Make sure you UNGROUP the sheets when finished - either right-click on a sheet name and select "ungroup" or just click on the summary sheet

Happy to help