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, 2022 in Others by Kithuzzz
• 38,000 points
965 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, 2022 by narikkadan
• 63,600 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, 2022 in Others by gaurav
• 23,260 points
4,264 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, 2022 in Others by Kithuzzz
• 38,000 points
1,037 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, 2022 in Others by narikkadan
• 63,600 points
1,414 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, 2022 in Others by narikkadan
• 63,600 points
1,780 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, 2022 in Others by narikkadan
• 63,600 points
1,999 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, 2022 in Others by narikkadan
• 63,600 points
650 views
0 votes
1 answer

Newbie needs Excel suggestions with a simple script

Formula to Count the Number of Occurrences ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
513 views
0 votes
1 answer

Excel: Count distinct numerical values if string condition matches

FILTER Shipper and Month based on Employee ...READ MORE

answered Feb 10, 2023 in Others by narikkadan
• 63,600 points
475 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, 2022 in Others by narikkadan
• 63,600 points
630 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, 2022 in Others by narikkadan
• 63,600 points
458 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