SUMIF com Range de Data

rodmorais

New Member
Joined
May 17, 2020
Messages
4
Office Version
365
Platform
Windows
Prezados,

Tenho uma tabela que na coluna A temos data no formato abaixo:

2020/05/26 13:16:29
2020/05/26 13:20:58
2020/05/26 14:51:13
2020/05/26 16:04:46
2020/05/27 10:11:45
2020/05/27 10:36:10

Tenho várias ocorrências cuja data é no mesmo dia, então quero somar todas as ocorrências de um mesmo dia. Estou usando a formula =sumif(A3:A,TODAY(),M3:M) ou =sumif(A3:A,"2020/05/26",M3:M) mas está retornando zero. Onde estou errando? Será que o fato da hora estar presente está atrapalhando?

Agradeço antecipadamente,
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

The_Macrotect

Board Regular
Joined
Dec 11, 2017
Messages
89
Hello,

There may be multiple ways to do this, but this was what I came up with:

1) Insert a helper column before column A

Insert Column.jpg



2) In Cell A1, type the formula:
Code:
=TEXT(LEFT(B1,LEN(B1)),"m/dd/yyyy")

This formats all of the data without the time. Your worksheet should now look like this:
Excel 2007 32 bit
A
B
1
5/26/20205/26/2020 13:16
2
5/26/20205/26/2020 13:20
3
5/26/20205/26/2020 14:51
4
5/26/20205/26/2020 16:04
5
5/27/20205/27/2020 10:11
6
5/27/20205/27/2020 10:36
Sheet: Sheet1

3) Now use this formula to count each time a certain date occurs:
Code:
=COUNTIF(A1:A6,"5/26/2020")

4) Hide Column A (Optional)
Hide Column.jpg



Final product:
Final.jpg
 

Watch MrExcel Video

Forum statistics

Threads
1,102,561
Messages
5,487,567
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top