Excel COUNTIF Column D year equals 2015 and Column L equals 15 or greater

0 votes

I can determine how many cells in Column D with the year 2015 there are. And I can count the number of cells in Column L that are 15 or larger. I want to merge these two formulas, but I'm not sure how to do it.

The formula I use to count the years is:

=SUMPRODUCT(--(TEXT('Sheet1'!D:D,"YYYY")="2015"))

The formula for counting 15 or greater is:

=COUNTIF('Sheet1'!L:L,">=15")

Can someone please provide a way I can combine these two formulas so I can count how many instances there was 15 or greater for the year 2015?

Oct 2 in Others by Kithuzzz
• 20,660 points
66 views

1 answer to this question.

0 votes

COUNTIFS() with an S allows the ability of multiple criteria and you can bracket the dates:

=COUNTIFS('Sheet1'!L:L,">=15",'Sheet1'!D:D, ">=" & DATE(2015,1,1),'Sheet1'!D:D, "<=" & DATE(2015,12,31))

enter image description here


If your dates are actually text strings that look like dates you can do the:

=COUNTIFS('Sheet1'!L:L,">=15",'Sheet1'!D:D,"*2015*")
answered Oct 3 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

datatable remove column on export to pdf and excel

When we are using jquery datatable for ...READ MORE

answered Feb 17 in Others by gaurav
• 22,040 points
1,904 views
0 votes
0 answers

Format an Excel column (or cell) as Text in C#?

When I copy values from a data ...READ MORE

Oct 31 in Others by Kithuzzz
• 20,660 points
61 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
41 views
0 votes
1 answer

Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF($A$1:$A$8,"A"),COUNTIF($A$1:$A$8,"HA")/2) - total count ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
90 views
0 votes
1 answer

How to simplify adding multiple countifs formula in excel

If the Excel version you are using ...READ MORE

answered Nov 26 in Others by narikkadan
• 37,660 points
24 views
0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
311 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
145 views
0 votes
1 answer

Excel - add all X in column and then multiply

Use countif for the count then multiply ...READ MORE

answered Oct 1 in Others by narikkadan
• 37,660 points
50 views
0 votes
1 answer

Which is better normal desktop window or MDI like excel and chronos

How people interact with your system entirely ...READ MORE

answered Nov 4 in Others by narikkadan
• 37,660 points
34 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP