COUNTIFS Help

Leo_T

New Member
Joined
Jul 4, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Good evening,

First post here from a moderate Excel user in need of assistance please, I am trying to create a project burn down chart against specific project manager names. Using the sample data table I have attached I want to insert a COUNTIFS formula in cells F3 to I10 of the chart table that:
  • counts the number of projects against a specific name in column A;
  • then counts how many of those projects fall in a calendar month set out in column B;
  • where there is no date in column B and its blank I need to then count the date from column C instead.
  • Excel Help.png
I can write the formulas required to return values for bullets one and two as below, but have struggled with a formula for column three or joining them all up into one. Any help appreciated.

Many thanks,
Leo
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,991
Office Version
  1. 365
Platform
  1. Windows
Will there only be 2 date columns in the real data, or are there going to be a lot more?

Based on the capture above, the most practical way would be to enter this into F3, then fill the table from there.
Excel Formula:
=COUNTIFS($A$3:$A$10,F$2,$B$3:$B$10,$E3)+COUNTIFS($A$3:$A$10,F$2,$C$3:$C$10,$E3)
 

Leo_T

New Member
Joined
Jul 4, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thank you Jason, its much appreciated.

There are in excess of 30 columns in the data set I am working from, many of which are date fields but I only want to pull data from 2 date columns to produce a chart. I've included the dummy data as an example.

I've tried the formula you suggest I have found that it totals all dates for a specific month across both columns B and C, however I only want the formula to count a date from column C in the event that the cell in Column B for the same row is blank - that is where I have struggled.

It also only returns dates that exactly match the 1st of each month as the dates in column E are all the first of the month formatted as mmm-yy. I want to count all dates in a calendar month so for example would use the formula =COUNTIFS(E3,">=",E4,"<")

Hope that help guys.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,991
Office Version
  1. 365
Platform
  1. Windows
Can you add an extra column to the base data to drive the results? Trying to get the results directly to the table is not going to be a simple task, especially with excel 2016.

My way of doing based on what you have said above would be to enter
Excel Formula:
=EOMONTH(IF($B3="",$C3,$B3),-1)+1
into D3, then fill it down to the bottom of the base data table. Then enter
Excel Formula:
=COUNTIFS($A:$A,F$2,$D:$D,$E3)
into F3 to populate the table.
 
Solution

Leo_T

New Member
Joined
Jul 4, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Awesome, that worked a treat.

huge thanks for solving my issue :)
 

Forum statistics

Threads
1,144,611
Messages
5,725,304
Members
422,608
Latest member
bswg5882

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top