Solved SUMIFS Function Problem

[Emma35]

Hi Everyone,
Could someone have a look at this function and tell me why it's not working ?

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!,L2:L30747,"Late",MainData!,P2:30747,"2025-01")

Some background info
The formula i'm working on is in the same workbook as MainData but not on the same worksheet
MainData is a worksheet which contains the raw information
I'm trying to sum the amount of times a delivery was Late for Section A in period 2025-01
When a delivery is late it is marked with a '1'

Thanks a lot,
Em

 

[cheekybuddha]

When a delivery is late it is marked with a '1'

What is the difference between column N and column L ?

Does every late entry have value 1 in column N and string 'Late' in column L ?

 

[Emma35]

What is the difference between column N and column L ?

Does every late entry have value 1 in column N and string 'Late' in column L ?

Hi David....yes i'm sorry that's correct. I should have explained it a bit better. Column N will either have a '1' representing Late or '0' representing Ok

 

[cheekybuddha]

Also, you may have an error in you formula:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!,L2:L30747,"Late",MainData!,P2:30747,"2025-01")
                                                                   ^                          ^
                                                                   |                          |
                                                                   should not have commas here

 

[cheekybuddha]

Hi David....yes i'm sorry that's correct. I should have explained it a bit better. Column N will either have a '1' representing Late or '0' representing Ok

What is point of column L then?

 

[arnelgp]

column N can be either 1 (late) or 0 (ok), but on your formula you are summing that column.
explain the rest of the columns.

syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n])

 

[Emma35]

What is point of column L then?

This is a worksheet i 'inherited' and can't change so i just have to work with it.

 

[Emma35]

column N can be either 1 (late) or 0 (ok), but on your formula you are summing that column.
explain the rest of the columns.

syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n])

I'm summing all the number '1' values to get a total of how many deliveries were late.
The other columns just denote the production area (Section) and time period (01-2025) which is Jan 2025

 

[arnelgp]

take a closer look at column P , you SumIfs is expecting a "string" there (not a real date).
also see comment on post#4, you have extra comma in your formula.

 

[Emma35]

Also, you may have an error in you formula:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!,L2:L30747,"Late",MainData!,P2:30747,"2025-01")
                                                                   ^                          ^
                                                                   |                          |
                                                                   should not have commas here

Ok i took those commas out and my formula is below but no luck

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!L2:L30747,"Late",MainData!P2:30747,"2025-01")

 

[Emma35]

take a closer look at column P , you SumIfs is expecting a "string" there (not a real date).
also see comment on post#4, you have extra comma in your formula.

Ok so do i need to use different syntax because it's a date ?

 

[cheekybuddha]

Still trying to check - both column L will say 'Late' and column N will say 1 at the same time?

If so, you probably don't need both columns as critieria, just use one of them.

Arnel makes a good point about the date - could 2025-01 just be a format applied to an actual value of 2025-01-01 ?

In any case, try building up in stages:

First try:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A")

Then try:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!P2:30747,"2025-01")

Then try:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!P2:30747,DATE(2025,01,01))

 

[arnelgp]

Arnel makes a good point about the date - could 2025-01 just be a format applied to an actual value of 2025-01-01 ?

Or the column is real date and formatted only to "YYYY-MM" so you need a range there between 2025-01-01 and 2025-01-31.

 

[Emma35]

Ok there's another column (B) which contains an actual date but column (P) contains the formula below (which i'm told converts the date to a period) eg: January in this case so all the late deliveries for a particular period can be counted. So the date 28/01/25 is converted to 2025-01

=YEAR($B30723)&"-"&TEXT(MONTH(B30723),"00")

 

[cheekybuddha]

Ok i took those commas out and my formula is below but no luck

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!L2:L30747,"Late",MainData!P2:30747,"2025-01")

Your range here is still wrong and I didn't spot it with my suggestions in Post #12

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!L2:L30747,"Late",MainData!P2:30747,"2025-01")
                                                                                                ^
                                                                                                |
                                                                                        Missing 'P'

 

[Emma35]

Your range here is still wrong and I didn't spot it with my suggestions in Post #12

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!L2:L30747,"Late",MainData!P2:30747,"2025-01")
                                                                                                ^
                                                                                                |
                                                                                        Missing 'P'

I replaced the P and no difference

 

[Emma35]

Still trying to check - both column L will say 'Late' and column N will say 1 at the same time?

If so, you probably don't need both columns as critieria, just use one of them.

Arnel makes a good point about the date - could 2025-01 just be a format applied to an actual value of 2025-01-01 ?

In any case, try building up in stages:

First try:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A")

Then try:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!P2:30747,"2025-01")

Then try:

=SUMIFS(MainData!N2:N30747,MainData!C2:C30747,"Section A",MainData!P2:30747,DATE(2025,01,01))

It's not even working for the first criteria "Section A". I'm just seeing the formula in the cell when i press Enter ?

 

[cheekybuddha]

I'm just seeing the formula in the cell when i press Enter ?

You're sure you have the '=' at the beginning of the formula in the formula bar?

(Sorry to ask silly questions - remember, we cant see your screen!)

 

[Emma35]

No it's a fair question. Formula below

 

[Emma35]

Well it looks like you were right to ask a 'silly' question because i've just worked out the problem. The cell where i was entering the formula was (for some reason) formatted as text. Honestly i never even thought of checking something so basic. My fault for not checking the obvious things first.
Thank you David and arnelgp for helping me out....i appreciate it a lot

Em x