• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Excel Sumifs Formula

Good morning,

I have a problem that I can't seem to figure out. I have attached a same section of the spreadsheet i am working on.

I need to write a formula that sum's column J on the "F-30 Data" tab. The formula is based on Sheet 1 so I put some place holders.

What I need is the formula to go out to that F-30 data tab,

1 - Look at column "A" if it is equal to 1334509
2 - Look at column "E" if it is equal to Z00
3 - Look at Column "N" if it is equal to "Saturday" or "Sunday"
4 - Look at Column "L" if it is not equal to 22 or 24 (so it would include everything else)

then sum column Column J.

I have tried everything and can't seem to get it to work.
book1.xls
0
dn920
Asked:
dn920
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
FernandoFernandesCommented:
type this in the cell:

=SUM(('F-30 Data'!A2:A150*1=$B$5*1)*('F-30 Data'!E2:E150="Z00")*(('F-30 Data'!N2:N150="Saturday")+('F-30 Data'!N2:N150="Sunday"))*('F-30 Data'!L2:L150<>22)*('F-30 Data'!L2:L150<>24)*'F-30 Data'!J2:J150)

Then close with Ctrl+Shift+Enter
0
 
Kakhaber SiradzeCommented:
=SUBTOTAL(9,'F-30 Data'!J2:J116)
book1.xls
0
 
dn920Author Commented:
Fernando,

I entered that, and am getting a result of 0, logically I can see what you are doing and it should be giving the result, but I am not sure why its coming back with a 0
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
FernandoFernandesCommented:
Did you finish with Ctrl+Shift+Enter instead of just Enter ?
0
 
barry houdiniCommented:
The trick when you have multiple "<>"s is to apply those separately in SUMIFS, this formula should give you the required result

=SUM(SUMIFS('F-30 Data'!J:J,'F-30 Data'!A:A,B5,'F-30 Data'!N:N,{"Saturday","Sunday"},'F-30 Data'!E:E,"Z00",'F-30 Data'!L:L,"<>22",'F-30 Data'!L:L,"<>24"))

regards, barry
0
 
FernandoFernandesCommented:
see the attached file with the solution
book1.xls
0
 
dn920Author Commented:
Ok I did not do the crtl shift and enter sorry, but when I did that just now it throws {} around the entire thing and shows N/A giving me an error. this wouldn't be another perk of 2007 would it?
0
 
dn920Author Commented:
Fernando and Berry both are working, thanks so much for your assistance on this, looking at what you all put together makes sense to me I was trying to do too much at once instead of spreading it out.


Thanks!!
0
 
barry houdiniCommented:
You can use SUMIFS as per my suggestion above - then you don't need CSE......another (shorter) option would be to sum just for days beginning with "S", i.e.

=SUMIFS('F-30 Data'!J:J,'F-30 Data'!A:A,B5,'F-30 Data'!N:N,"S*",'F-30 Data'!E:E,"Z00",'F-30 Data'!L:L,"<>22",'F-30 Data'!L:L,"<>24")

regards, barry
0
 
FernandoFernandesCommented:
When you finish the formula with Ctrl+Shift+Enter, you're telling Excel to understand that you're writing an array formula instead of a simple formula. Those { } at the edges means Excel will work differently with the formula. I don't understand why you're getting #N/A... can you post your file again, with the formula? Or, you can also see the example that I posted...

also, try to use Barry's solution.
0
 
krishnakrkcCommented:
Hi,

=SUM(SUMIFS('F-30 Data'!J:J,'F-30 Data'!N:N,{"Saturday","Sunday"},'F-30 Data'!E:E,"Z00",'F-30 Data'!L:L,{"<>22","<>24"}))

Kris
0
 
krishnakrkcCommented:

Sorry, didn't see the replies. :(



0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now