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

#### xfigo

##### New Member
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):

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

### 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.

Replies
4
Views
94