why showing as blank not zero

AYSHANA

Board Regular
Joined
Oct 16, 2021
Messages
90
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
my table contains formulas , but why in F4:F8 is showing as blank instead of zero, it should be like E4:E8 or D4:D8.
and also its not calculating, i tried to write the formula again but still im getting same issue.
thank you.


COMBINE vba .xlsm
BDEF
3FEBMARAPR
4Received BGRPS2410 
5Received XM580 
6Received IAT10 
7Received DAT00 
8Received BGRP30 
Summary
Cell Formulas
RangeFormula
D4D4=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$4),"")
E4:E8E4=IF($V$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$V$2,'raw daily'!$G$2:$G$3000,"<="&$V$3,'raw daily'!$B$2:$B$3000,$S4),"")
F4F4=IF($W$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$W$2,'raw daily'!$G$2:$G$3000,"<="&$W$3,'raw daily'!$B$2:$B$3000,$S4),"")
D5D5=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$5),"")
F5:F8F5=IF($W$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$W$2,'raw daily'!$G$2:$G$30000,"<="&$W$3,'raw daily'!$B$2:$B$30000,$S5),"")
D6D6=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$6),"")
D7D7=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$7),"")
D8D8=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$8),"")
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Notice that the end of your formula for F4, if you IF condition is not being met, you are telling it to return a blank, not a zero:
Rich (BB code):
=IF($W$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$W$2,'raw daily'!$G$2:$G$3000,"<="&$W$3,'raw daily'!$B$2:$B$3000,$S4),"")

If you want it to return a zero instead, use:

Rich (BB code):
=IF($W$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$W$2,'raw daily'!$G$2:$G$3000,"<="&$W$3,'raw daily'!$B$2:$B$3000,$S4),0)
 
Upvote 0
but for D4 and E4 its returning blank and its working
 
Upvote 0
but for D4 and E4 its returning blank and its working
The formulas in D4 and E4 aren't using the same columns in your conditions!
So you cannot assume that it will work for the formula in column F4, since it is considering totally different data.
And you have not shown us what is in columns U, V, and W, so we cannot tell you why your formula is hitting the FALSE part instead of the TRUE part.
You have to show us ALL the data that your formula references if you want us to evaulate your formula for you.
 
Upvote 0
my data is not calculating from month APR to DEC


Cell Formulas
RangeFormula
T2T2=DATE($S$1,ROWS($T$2:$T$2),ROWS($T$2:$T$2))
U2U2=DATE($S$1,ROWS($T$2:$T$3),ROWS($T$2:$T$2))
V2V2=DATE($S$1,ROWS($T$2:$T$4),ROWS($T$2:$T$2))
W2W2=DATE($S$1,ROWS($T$2:$T$5),ROWS($T$2:$T$2))
X2X2=DATE($S$1,ROWS($T$2:$T$6),ROWS($T$2:$T$2))
Y2Y2=DATE($S$1,ROWS($T$2:$T$7),ROWS($T$2:$T$2))
Z2Z2=DATE($S$1,ROWS($T$2:$T$8),ROWS($T$2:$T$2))
AA2AA2=DATE($S$1,ROWS($T$2:$T$9),ROWS($T$2:$T$2))
AB2AB2=DATE($S$1,ROWS($T$2:$T$10),ROWS($T$2:$T$2))
AC2AC2=DATE($S$1,ROWS($T$2:$T$11),ROWS($T$2:$T$2))
AD2AD2=DATE($S$1,ROWS($T$2:$T$12),ROWS($T$2:$T$2))
AE2AE2=DATE($S$1,ROWS($T$2:$T$13),ROWS($T$2:$T$2))
T3:AE3T3=EOMONTH(T2,0)+"23:59"
D4D4=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$4),"")
E4:E8E4=IF($V$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$V$2,'raw daily'!$G$2:$G$3000,"<="&$V$3,'raw daily'!$B$2:$B$3000,$S4),"")
F4F4=IF($W$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$W$2,'raw daily'!$G$2:$G$3000,"<="&$W$3,'raw daily'!$B$2:$B$3000,$S4),"")
G4:G8G4=IF($X$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$X$2,'raw daily'!$G$2:$G$30000,"<="&$X$3,'raw daily'!$B$2:$B$30000,$S4),"")
H4:H8H4=IF($Y$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$Y$2,'raw daily'!$G$2:$G$30000,"<="&$Y$3,'raw daily'!$B$2:$B$30000,$S4),"")
I4:I8I4=IF($Z$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$Z$2,'raw daily'!$G$2:$G$30000,"<="&$Z$3,'raw daily'!$B$2:$B$30000,$S4),"")
J4:J8J4=IF($AA$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$AA$2,'raw daily'!$G$2:$G$30000,"<="&$AA$3,'raw daily'!$B$2:$B$30000,$S4),"")
K4:K8K4=IF($AB$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$AB$2,'raw daily'!$G$2:$G$30000,"<="&$AB$3,'raw daily'!$B$2:$B$30000,$S4),"")
L4:L8L4=IF($AC$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$AC$2,'raw daily'!$G$2:$G$30000,"<="&$AC$3,'raw daily'!$B$2:$B$30000,$S4),"")
M4:M8M4=IF($AD$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$AD$2,'raw daily'!$G$2:$G$30000,"<="&$AD$3,'raw daily'!$B$2:$B$30000,$S4),"")
N4:N8N4=IF($AE$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$AE$2,'raw daily'!$G$2:$G$30000,"<="&$AE$3,'raw daily'!$B$2:$B$30000,$S4),"")
O4:O8O4=SUM(D4:N4)
D5D5=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$5),"")
F5:F8F5=IF($W$2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$30000,">="&$W$2,'raw daily'!$G$2:$G$30000,"<="&$W$3,'raw daily'!$B$2:$B$30000,$S5),"")
D6D6=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$6),"")
D7D7=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$7),"")
D8D8=IF($U2<=TODAY(),COUNTIFS('raw daily'!$G$2:$G$3000,">="&$U2,'raw daily'!$G$2:$G$3000,"<="&$U3,'raw daily'!$B$2:$B$3000,$S$8),"")
 
Upvote 0
Well, we still don't know what you data on the "raw daily" sheet looks like (we would need to see data in column B and G), but I do see one thing that sticks out like a sore thumb.

One the Summary sheet data you did post, it looks like all your entries in cells T2:AE3 are in fact text and NOT valid dates (the little orange triangles in the upper left corner of each cell seems to suggest that). So so since you are trying to compare TODAY() (which is a valid date) to text entries, I am not surprised you are not getting expected results.
 
Upvote 0
any idea hoe to solve it

COMBINE vba .xlsm
BG
1OrdersCRA Receive Date Time
2BGRPS14-02-23 16:47
3BGRPS14-02-23 13:54
4BGRPS14-02-23 4:35
5BGRPS14-02-23 20:19
6BGRPS14-02-23 16:05
7BGRPS14-02-23 14:01
8BGRPS14-02-23 14:10
9BGRPS14-02-23 10:32
10BGRPS14-02-23 2:34
11BGRPS14-02-23 12:39
12BGRPS14-02-23 16:36
13BGRPS14-02-23 20:44
14BGRPS14-02-23 5:19
raw daily
 
Upvote 0
im trying to count how order was received in a month
 
Upvote 0
I don't know what value the Today part is adding, it seems unlikely you would have a receive date later than today.
I also don't understand what all the convoluted date stuff out to the right is doing for you.
Here are 2 options:
Option 1

20230304 CountIfs Months AYSHANA.xlsx
BCDEFGHIJKLMNOPQRS
1Year >2023
2
3FEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTOTAL
4Received BGRPS1210000000000BGRPS
5Received XM000000000000%XM
6Received IAT000000000000IAT
7Received DAT000000000000DAT
8Received BGRP000000000000BGRP
Summary
Cell Formulas
RangeFormula
D4:O8D4=COUNTIFS('raw daily'!$B$2:$B$14, $S4, 'raw daily'!$G$2:$G$14, ">="&DATEVALUE("1-"&D$3&"-" & $E$1), 'raw daily'!$G$2:$G$14, "<"&EDATE(DATEVALUE("1-"&D$3&"-" & $E$1),1))


Option 2 - If you make you headings actual dates formatted as MMM, it wil simplify the formula

20230304 CountIfs Months AYSHANA.xlsx
BCDEFGHIJKLMNOPQRS
1
2
3FebMarAprMayJunJulAugSepOctNovDecTOTAL
4Received BGRPS1210000000000BGRPS
5Received XM000000000000%XM
6Received IAT000000000000IAT
7Received DAT000000000000DAT
8Received BGRP000000000000BGRP
Summary Date Hdg
Cell Formulas
RangeFormula
E3:N3E3=EDATE(D3,1)
D4:O8D4=COUNTIFS('raw daily'!$B$2:$B$14, $S4, 'raw daily'!$G$2:$G$14, ">="&D$3, 'raw daily'!$G$2:$G$14, "<"&EDATE(D$3,1))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top