Calculating data based on Today date

[syud_zack]

hi all,

i'm new in Excel macro..

i'm facing the problem on calculating sum based on today date..

currently, i'm using this formula and it didn't work..

={SUM((Data!$B$5:$B$19696=TODAY())*(Data!$C$5:$C$19696=1)*(Data!$E$5:$E$19696=4))}

can any tell me, where's i'm wrong??

 

[Michael M]

Hi and Welcome to the Board
Have a look at SUMIF and COUNTIF instead of SUM
Maybe something like

=SUMIF(DATA!$B$5:$B$19696,TODAY())*SUMIF(DATA!$C$5:$C$19696,"1")*SUMIF(DATA!$E$5:$E$19696,"4")

 

[syud_zack]

i've tried SUMIF and COUNTIF before. but it keeps give me 0 for the calculation..

i don't know where i'm wrong

 

[Michael M]

Probably because the first part of your SUM is trying to sum the date.
Should you maybe be trying to COUNT the number of occurrences of Today ()

 

[syud_zack]

Very Satisfied 0
Fairly Satisfied 0
Neither satisfied 0
Fairly dissatisfied 0
Very dissatisfied 0
Total 0

i want to be able to calculate number of response based on those criteria. the raw data is on he other sheet name Data. it always give the wrong answer. i've try count as u mentioned, it give me wrong answer

=COUNT((Data!$B$5:$B$19696=TODAY())*(Data!$C$5:$C$19696=1))

 

[Michael M]

Ok, I'd suggest you post a small sample of your sheet with an expected result.
Also, Have a look at my previous posts regarding COUNTIF.....not COUNT
and SUMIF.....not SUM