I have a spreadsheet with:

names in column A

dates in column B

"Yes" or "No" in column C

Spreadsheet captures compliance of staff to complete work in time Yes if they achieved it and No if not. I would like excel to count the number of times and employee achieved a yes and when they achieved a No so I can then calculate a compliance percentage. There can be up to 30 rows for each employee and over 40 employees

Here, paste this formula in D1 then copy it down to the last row:

=SUM(IF($A$1:$A$30=A1,IF($C$1:$C$30="Yes",1,0)))

MatRex

Posted on May 20, 2008

Hi this my id :dadu_mf@rediff.com plz send excel material

Posted on Mar 27, 2008

It is possible you have locked the spreadsheet. I would check the spreadsheet to see if you have locked or protected the rows or columns or, maybe even the entire spreadsheet.

May 17, 2011 | Microsoft Office Professional 2007 Full...

Hi JK,

We have to use the combination of dynamic list and indirect() function.

and offcourse countif() and if().

If you need the solution from me, Please send me the sample sheet on ali_zulfikar@yahoo.com so that I can send you the solution sheet.

Zulfikar Ali

We have to use the combination of dynamic list and indirect() function.

and offcourse countif() and if().

If you need the solution from me, Please send me the sample sheet on ali_zulfikar@yahoo.com so that I can send you the solution sheet.

Zulfikar Ali

Apr 06, 2010 | Microsoft Office Excel 2003 for PC

Here is a solution that might work for you. Please be mindful that there are several different solutions that will achieve the same thing in Excel (I am assuming Excel is your software).

1) Make sure each column in your spreadsheet has a heading.

2) Highlight (select) the column of birthdays.

3) Choose Format from the menu bar.

4) Choose Cells... from the drop-down menu.

5) For the "category", choose Custom.

6) In the "type" box, type yyyy

7) Click OK and when you return to your spreadsheet, you will see only the years displayed. However, when you try to edit a cell, you will see that the entire birth date is stored and preserved.

8) Now, highlight the entire table

9) Then, choose Data from the menu bar

10) Choose Subtotals... from the drop-down menu

11) In the "At each change in" box, choose the name of the column with the birth dates

12) In the "Use function" box, choose Count

13) Leave all other choices at their defaults

14) Click OK, and you are done.

Using this method, you can continue to enter the data as you always have. In that way, the birth date info is never actually destroyed or converted. What you are doing is simply deciding what is displayed.

I hope this helps.

1) Make sure each column in your spreadsheet has a heading.

2) Highlight (select) the column of birthdays.

3) Choose Format from the menu bar.

4) Choose Cells... from the drop-down menu.

5) For the "category", choose Custom.

6) In the "type" box, type yyyy

7) Click OK and when you return to your spreadsheet, you will see only the years displayed. However, when you try to edit a cell, you will see that the entire birth date is stored and preserved.

8) Now, highlight the entire table

9) Then, choose Data from the menu bar

10) Choose Subtotals... from the drop-down menu

11) In the "At each change in" box, choose the name of the column with the birth dates

12) In the "Use function" box, choose Count

13) Leave all other choices at their defaults

14) Click OK, and you are done.

Using this method, you can continue to enter the data as you always have. In that way, the birth date info is never actually destroyed or converted. What you are doing is simply deciding what is displayed.

I hope this helps.

Aug 02, 2009 | Microsoft Office Excel 2007

In the first row of numbers, assume row 3 for example (leaving 2 rows for titles, put in cell E3: =C3+D3. In the next row (assuming row 4, put in cell E4: =C4+D4+E3. Use fill down to populate this formula all the way down.

Jun 18, 2009 | Microsoft Excel for PC

Sounds like there is a gap between the filter heading and the column data. That's the only time I've seen suggested data missing from the drop-down.

Jun 16, 2009 | Microsoft Business & Productivity Software

One way of finding (and removing) duplicate entries is to sort the column and put a simple formulate in a temporary column next to that column; for example - if column A has duplicates, insert a column (B) and starting in B2 put if(A2=A1,"DUP",""). Select B2 and scroll down to the bottom of your spreadsheet. Press <ctrl>-D to extend the formula in B2. Wherever there is a duplicate you'll see "DUP" in column B. If you want to remove the duplicates copy column B and Edit / Paste Special... with "values" selected (to wipe out the formula). You can then sort the spreadsheet on column B and remove rows with DUP in column B.

If you can't delete the duplicate rows and the order is important first include a column that captures the order - same trick except put row() in that column, copy / paste special the values and then you can re-sort after doing the above to have both the DUPs marked and the original order.

Hope that helps.

If you can't delete the duplicate rows and the order is important first include a column that captures the order - same trick except put row() in that column, copy / paste special the values and then you can re-sort after doing the above to have both the DUPs marked and the original order.

Hope that helps.

Aug 08, 2008 | Microsoft Business & Productivity Software

The solution I've used in similar situations is to create a 3rd column C with the items in column A and column B concatenated.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

May 27, 2008 | Microsoft Excel for PC

you select the trial balace--and press Alt+E or click export---then the export details screen came,

Language: Default(all language)

Format: Excel (spreadsheet)

Output file name: trial bal.xls (any name you select)

Excel (spreadsheet) formating: yes

other details you select and accept the screen. then minimise or close the Tally, you open the tally file name at my computer. at the time you can see the file trialbal.

Language: Default(all language)

Format: Excel (spreadsheet)

Output file name: trial bal.xls (any name you select)

Excel (spreadsheet) formating: yes

other details you select and accept the screen. then minimise or close the Tally, you open the tally file name at my computer. at the time you can see the file trialbal.

Mar 20, 2008 | Business & Productivity Software

Since you are searching the data by the phone number , first select all the data in the spreadsheet and sort it in ascending order by the phone number.

Then, assuming you have 5 columns of data A through E, and the phone numbers are in column E, with row 1 occupied by column headings, use the following formula in cell F2=IF(E2=E1,"Duplicate",1)

Drag this formula down column F till the end of your data

Select the entire data and do an auto filter

In column F filter the data by Duplicate and delete all these rows

What remains should be unique data

Then, assuming you have 5 columns of data A through E, and the phone numbers are in column E, with row 1 occupied by column headings, use the following formula in cell F2=IF(E2=E1,"Duplicate",1)

Drag this formula down column F till the end of your data

Select the entire data and do an auto filter

In column F filter the data by Duplicate and delete all these rows

What remains should be unique data

Dec 19, 2007 | Business & Productivity Software

Use the =COUNTIF function

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")

or

@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in

column a from row 1 to row 100.

Mike

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")

or

@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in

column a from row 1 to row 100.

Mike

Oct 31, 2007 | Microsoft Excel for PC

Jan 28, 2016 | Microsoft Excel for PC

139 people viewed this question

Usually answered in minutes!

Update.....

I've managed to make significant progress with this formula entered as an array formula:

{=SUM((A2:A391="Smith, John")*(C2:C391="Yes"))}

However, I have found that the formula does not work if I use a cell reference instead of the persons name meaning I need to create each formula individually - quite a chore if you have 40 people and 4 formulas for each one. Does anyone know how I can use a cell reference instead of a text string for the name ?

Sorry for wasting your time if you've started looking in to this for me. It seems if I move the formula to new sheet with names and reference the sheet with raw data then I can use a cell reference for the name:

=SUM((Reports!$B$2:$B$391=A3)*(Reports!$J$2:$J$391="Yes"))

So this formula sits in a table where the names are in column A and the raw data is in the spreadsheet titled Reports.

i have some amount in a row like below mention

Amount200,000.00

365,000.00

185,000.00

400,000.00

40,000.00

200,000.00

495,000.00

300,000.00

835,000.00

795,000.00

& i want a figure of >100000 <=200000 amount.

how much figure of amount is grater then 100000 & less then 200000

please suggest me a formula

×