sumif dates returns 0 when file is opened in different Excel version (PC to mac)

xfigo

New Member
Joined
Sep 28, 2020
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
So, I'm about to loose it with this one... :)

I've sketched a file in my work pc (MS Professional Plus 2016) and when I open it in my mac (MS Excel for Mac 16.41) a formula is not returning the same value. In fact it returns "0".
Maybe this is simple, but I don't quite understand what to do. May be something to do with dates, maybe version issue (PC to mac).


On a given sheet I'm fetching Sums given the dates of the rows (month yyyy):

=SUMIF(Table2[MÊS ADMISSÃO];J$5;Table2[PROV Urgência])

Where

[MÊS ADMISSÃO] cells are given by =VLOOKUP(MONTH([@[DATA DE REFERENCIAÇÃO]]);Values!$W$4:$X$15;2;FALSE)&" "&YEAR([@[DATA DE REFERENCIAÇÃO]]))
AND
[DATA DE REFERENCIAÇÃO] : cells with dates dd/mm/yyyy
AND
$W$4:$X$15 :

1 janeiro
2 fevereiro
3 março
4 abril
5 maio
6 junho
7 julho
8 agosto
9 setembro
10 outubro
11 novembro
12 dezembro



J$5: Setembro 20 (given by 01/09/2020 format is date)


[PROV Urgência] is column with values "1" and "0" given by a simple IF formula


So, in the PC the formula returns the sum of "1" when the date matches. In the mac I always get "0".
The only way round is if instead of J$5 as date (01/09/2020 formatted as date to Setembro 20) I use "Setembro 2020" (format: General)


Don't know if this was a messy explanation.
Can you help?

Many thanks
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

Threads
1,114,663
Messages
5,549,290
Members
410,908
Latest member
Allen P
Top