Excel Formula HELP!

[amandarose80]

This will take some explaining. We have a Tax Return Log Book that we keep here at my firm. It has the client names, the date the info came in, intials of who prepared it, intials of who reviewed, intials of biller.
I want to keep track of how many there are TO BE prepared, reviewed and billed. I tried this with a count formula and couldn't get it to work. This is over 26 sheets (A-Z), lines 7-175 on each sheet. I wanted a total to return to a stats sheet.
Here is the reasoning behind the formula:

If preparer(K7)=a blank AND reviewer(M7)=a blank then it needs to be prepared

If preparer(K7)=some intials AND reviewer(M7)=a blank then it needs to be reviewed

If preparer(K7)=some intials AND reviewer(M7)=some intials then it needs to be billed

Is there ANYWAY to accomplish this???

amccammack@vlcpa.com

THANK YOU!!!

 

[Wray69]

Amandarose80,

You can try something like this;

=IF(AND(ISBLANK(K7),ISBLANK(M7)),"Prepared",IF(AND(ISTEXT(K7),ISBLANK(M7)),"REVIEWED",IF(AND(ISTEXT(K7),ISTEXT(M7)),"BILLED&quot))

This should work for what you are looking for.

Regards -

Wray

 

[amandarose80]

That worked for that. I can put that on each sheet and hide the cells (so I won't confuse anyone). But now I try to use this formula to get a total for all sheets on the stats sheet and it doesn't work:
=COUNTIF(A:Z!Z7:Z175,"Prepared&quot

 

[Wray69]

do an array formula (you make and array formula by hitting CTRL+SHIFT+ENTER at the end og the formula)

=SUM(IF(A:Z!Z7:Z175="prepared",1,0))

this should work.

Regards -

Wray

 

[PepeLeMokko]

Did you try a pivot table with multiple consolidation ranges ?
When hiding unnecessary fields you would obtain the total you need, and its's updated each time you open the workbook.
Regards
André

 

[amandarose80]

I get a #REF! error when I use your formula, Wray69.

Coyteooc, I want a total on one sheet that comes from all 26 sheets. This is a shared workbook also.

 

[GlennUK]

The easiest way to collate this is on one sheet to have formulae to get data from the other sheets. This need not involve much formula creation, as if you use the 3 formula below, they can be copied for use fetching data from all the data sheets.

These formula use the INDIRECT function to convert a string into a reference to a sheet, so the entries in column A should be the sheet names ( A thru Z ), and then create these formulae in cells B2 C2 and D2 ( using Ctrl-Shift-Enter ), then copy down for all the sheets names.

You can then do a simply SUM at the bottom of each column to get the figures for each category.

In cell B2 do this formula to get the count of matches that "Need to be prepared" :
=SUM(IF(INDIRECT(A2&"!K7:K175&quot="",IF(INDIRECT(A2&"!M7:M175&quot="",1,0),0))

and in cell C2 do this formula for count of matches that "Need to be reviewed" :
=SUM(IF(INDIRECT(A2&&quot;!K7:K175&quot<>&quot;&quot;,IF(INDIRECT(A2&&quot;!M7:M175&quot=&quot;&quot;,1,0),0))

and in cell D2 do this formula for count of matches that
&quot;Need to be billed&quot; :
=SUM(IF(INDIRECT(A2&&quot;!K7:K175&quot<>&quot;&quot;,IF(INDIRECT(A2&&quot;!M7:M175&quot<>&quot;&quot;,1,0),0))

Hope this helps.
Cheers, Glenn.

 

[Wray69]

Amandarose,

If you want to email me at Wray69@attbi.com I will send you a sample spreadsheets that I mad with the formulas I gave you and maybe you will be able to see what is going wrong in yours giving you the #ref...

Regards -
Wray