Count Ifs that includes a date

wangaa11

New Member
Joined
Feb 28, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have a countif formula based on 3 different criteria but now I want to add in a 4th criteria and that is a date. The picture example below that I have columns B-E is the data and I want to fill in the chart for columns G-P. This is going to be an ongoing and daily sum, I will have to at the end of the day hard code the information with a copy past values. I thought about doing a countif with "today" in the formula but I don't want the data to duplicate, as you can see in the example there is a total column.

=IF(COUNTIFS($C$8:$C$13,"Banana",$D$8:$D$13,"Incoming",$E$8:$E$13,"Y")=0,"",COUNTIFS($C$8:$C$13,"Banana",$D$8:$D$13,"Incoming",$E$8:$E$13,"Y")) this is the formula I have in cell H8 currently.

1613569559859.png
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
do you only want to fill if the date are equal

=IF(COUNTIFS($C$8:$C$13,"Banana",$D$8:$D$13,"Incoming",$E$8:$E$13,"Y")=0,"",COUNTIFS($C$8:$C$13,"Banana",$D$8:$D$13,"Incoming",$E$8:$E$13,"Y",$B$8:$B$13, "H$7))

so for banana - 15th is 0 , 16th is 2 and 17th is 1 - BUT as incoming is Y
OR you could just add them all in 1 formula
 

wangaa11

New Member
Joined
Feb 28, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
do you only want to fill if the date are equal

=IF(COUNTIFS($C$8:$C$13,"Banana",$D$8:$D$13,"Incoming",$E$8:$E$13,"Y")=0,"",COUNTIFS($C$8:$C$13,"Banana",$D$8:$D$13,"Incoming",$E$8:$E$13,"Y",$B$8:$B$13, "H$7))

so for banana - 15th is 0 , 16th is 2 and 17th is 1 - BUT as incoming is Y
OR you could just add them all in 1 formula
It has to include the date in the example all would be 0 except for Feb 16th which would be 1. It has to be incoming and a Y for yes, does that make more sense now? Thank you for the help.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
thats what the formula should do
1 for 16th
But your showing a 1 under the 15th ?
 

wangaa11

New Member
Joined
Feb 28, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

thats what the formula should do
1 for 16th
But your showing a 1 under the 15th ?
That was just the initial formula I was using before I needed to add in the date. It can be disregarded completely just wanted to provide an idea of the formula I was initially using.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
715/02/202116/02/202117/02/202118/02/202119/02/202120/02/202121/02/202122/02/2021
816/02/2021BananaIncomingYBanana 1      
916/02/2021BananaIncomingNStrawberry        
1016/02/2021AppleOutgoingYApple        
1117/02/2021BananaIncomingN
1218/02/2021StrawberryIncomingN
1319/02/2021AppleOutgoingN
14
15
Main
Cell Formulas
RangeFormula
H8:O10H8=IFERROR(1/(1/COUNTIFS($C$8:$C$13,$G8,$D$8:$D$13,"Incoming",$E$8:$E$13,"Y",$B$8:$B$13, H$7)),"")
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT


However, you have another solution now
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,098
Members
416,957
Latest member
Brovashift

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