Excel Formula Help

stevengzn

Well-Known Member
Joined
Mar 1, 2007
Messages
166
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


1560347357107.png
 

I.am.Sam

rehabilitated troller
Joined
Jun 14, 2011
Messages
82,421
i cant help , your name is stevie G ..scared just now i slip up
 

EADC

Expert Member
Joined
Apr 10, 2018
Messages
4,481
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
 

Arex

Well-Known Member
Joined
Jun 4, 2017
Messages
201
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
Joined
Sep 4, 2018
Messages
725
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
Joined
Oct 18, 2007
Messages
838
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
Joined
Feb 15, 2008
Messages
4,159
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
Joined
Feb 3, 2014
Messages
25,782
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

Last edited:

Hamish McPanji

Honorary Master
Joined
Oct 29, 2009
Messages
38,211
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
 

Hamish McPanji

Honorary Master
Joined
Oct 29, 2009
Messages
38,211
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
Joined
Feb 3, 2014
Messages
25,782
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. :X3: I blame it being late.
 

KT-B

Honorary Master
Joined
Feb 3, 2014
Messages
25,782
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
Joined
Mar 6, 2004
Messages
26,430
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?
 
Top