# Excel Formula Help

#### stevengzn

##### Well-Known Member
Would appreciate some help on the below..

Lines 10-25 show a table with different faults and with an adjacent date next to it

I'm trying to get the cell highlighted in yellow to count all "FAULT TYPE 1" with a January date next to it.
I will then obviously replicate this for other fault types and in other months

thanks

Pivot table

#### I.am.Sam

##### rehabilitated troller
i cant help , your name is stevie G ..scared just now i slip up

#### I.am.Sam

##### rehabilitated troller
Pivot table
Add another column for month and pivot it

but the dates can group as well sometimes depend on date format

##### Expert Member
Add another column for month and pivot it

but the dates can group as well sometimes depend on date format
Yip just use =text(A11,"Mmmm") will extract month and then use month in your pivot under Column should work

#### HumanShield

##### Well-Known Member
Pivot table or COUNTIF formula if you really want to go that route

#### Arex

##### Well-Known Member
you could:

Create another column next the the dates with:
=MONTH(A11)
It will return the month in a number

then use COUNTIFS:

=COUNTIFS(B10:B22,A2,C10:C22,"1")
=(Range of faults, Fault type 1, Range of months in numbers, 1 for January)

or something like that, but i feel like this way is a hassle and there's probably much easier ways xD

#### joker08

##### Senior Member
create a new sheet with the data, another for the pivot table, pivot table has options to count the total faults in a given month, then you just have to link this cell to the first cell.
Using pivot table is the easiest method if you know what you are doing.

#### jambai

##### Senior Member
Should not be difficult .Countif might be easier than need to update data in pivot . Classify faults into month buckets( new column).Just if runs more than a year make sure months show years as well instead of just January -make it January 2019 bucket.

#### SeRpEnT

##### Expert Member
New column next to column C and add value 1 in each cell. Then @sumif to count the total values in this new column where column C matches the heading (fault type 1and so forth). Then conditional formatting to highlight cell in yellow if value is more than 0. Hope that makes sense.

#### KT-B

##### Honorary Master
Would appreciate some help on the below..

Lines 10-25 show a table with different faults and with an adjacent date next to it

I'm trying to get the cell highlighted in yellow to count all "FAULT TYPE 1" with a January date next to it.
I will then obviously replicate this for other fault types and in other months

thanks

View attachment 671213
Note I am using column B and not C

Firstly change all your months to the first of each month in row 1. Change the format to custom showing MMMM.
=COUNTIFS(\$A\$10:\$A\$36,">="&B\$1,\$A\$10:\$A\$36,"<="&EOMONTH(B\$1,0),\$B\$10:\$B\$36,\$A2)

That formula should work. If you have trouble - pm me your email and I will send you my sheet.

@RedViking thanks for the mention, been a bit busy today

NOTE: There is an error copying it to the other columns. But it is late and I will look again tomorrow.
Fixed the mistake. I had fixed the cell and not just the row. It should work perfectly now.

But pivot would work and as @Hamish McPanji - you can possibly get sumproduct to work - though I gave up on that one.

#### Attachments

• 92.5 KB Views: 18
Last edited:

#### Hamish McPanji

##### Honorary Master
Tip: put the raw data in another sheet. Then you can use columns instead of cell references. So you can add as many items as you want, and it will still work

#### KT-B

##### Honorary Master
Tip: put the raw data in another sheet. Then you can use columns instead of cell references. So you can add as many items as you want, and it will still work
Or could use name ranges

#### Hamish McPanji

##### Honorary Master
Or could use name ranges
I work with a lot of people who struggle with excel, separation is usually the best way. One mountain at a time

#### Hamish McPanji

##### Honorary Master
Note I am using column B and not C

Firstly change all your months to the first of each month in row 1. Change the format to custom showing MMMM.
=COUNTIFS(\$A\$10:\$A\$36,">="&\$B\$1,\$A\$10:\$A\$36,"That formula should work. If you have trouble - pm me your email and I will send you my sheet.

@RedViking thanks for the mention, been a bit busy today

NOTE: There is an error copying it to the other columns. But it is late and I will look again tomorrow.
Why don't you use the month function with sumproduct for the date calculation?

#### KT-B

##### Honorary Master
Why don't you use the month function with sumproduct for the date calculation?
I tried that but wasn't getting it working with the additional criteria. Sumproduct isn't one of my fave functions.

The only problem with mine is that I had fixed the whole cell instead of just the row for B1. I blame it being late.

#### KT-B

##### Honorary Master
Tip: put the raw data in another sheet. Then you can use columns instead of cell references. So you can add as many items as you want, and it will still work
That would make the ranges easier. But sadly reports sometimes require all data on one sheet.

This is why I love Excel. Same result can be achieved with different methods.

I fixed the formula btw. Stupid error.

#### Chris_the_Brit

##### High Tory
All this Excel nerd talk shows I hardly know anything about Excel!

Are there any good free courses out there to become an advanced user?