CODEWITHSUNDEEP
vbaexcelexcel-formula
NinoMH
NinoMH

Reputation: 21

Increment the value of a cell by 1 after checking values in a range of cells

I know this is as trivial and easy as it gets, but I can't figure it out for the life of me. I am completely clueless when it comes to excel formulas.

[Sheet image for reference2

I would like the cell U7 to increment by 1 for each "D" it finds in the selected range of cells (E7:S7), same with the cell V7, increment by 1 but for each "N" it finds in the same range of cells.

I was trying to use multiple if statements like:

=IF(E7:S7="D";1;0)

But that doesn't seem to work at all. Any help would be greatly appreciated.

Upvotes: 2

Views: 668

Answers (3)

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2693

You can use a =COUNTIF() Function.

You referred to VBA, so I assume you want to run this in a program possibly.

The code below will run through the used rows starting in row 7 and then insert the number of 'D" letters seen in the columns E to S.

 Sub CountNumberOfDLetters()

      Dim LastRow As Long
      With ActiveSheet.UsedRange
          LastRow = .Rows(.Rows.Count).Row
      End With

      Dim RowReference As Long
      Dim DataRange As Range
      Dim NumberOfD As Double

      For RowReference = 7 To LastRow

           Set DataRange = Range(Cells(RowReference, "E"), Cells(RowReference, "S"))
           NumberOfD = WorksheetFunction.CountIf(DataRange, "D")
           Cells(RowReference, "U").Value = NumberOfD

      Next RowReference

 End Sub

Upvotes: 0

Bradley Jones
Bradley Jones

Reputation: 61

This is what you are after. Use COUNTIF to count the number of times D or N occur in your range.

=COUNTIF(E7:S7,"D")

Upvotes: 3

Verzweifler
Verzweifler

Reputation: 940

You want to use the COUNTIF()-Function:

=COUNTIF(E7:S7, "D")

For more reference, see the Microsoft Office support site.

Upvotes: 0

Related Questions