Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

IFS command

0

Hi forum

I'm trying to use the IFS command - se below:

=SUM.IFS(A8:A40;C8:C40;"X")

The danish version is =SUM.HVISER(A8:A40;C8:C40;"X")

written as above is working fine, but my problem is, that I want to change the Criteria from "X" to something like <>"" (Not Empty Cell), how do I do that?

best regards 

Morten Magni

excel
MS Office Version: Office365
posted by: magnificent (rep: 4) on Fri Oct 13, 2023 at 11:18 am
Answer
Discuss

Answers

0
Selected Answer

Hi Morten

If your original formula was to sum values in column A if column C is "X", the formula should have no dot in the SUMIFS and use commas, so:

=SUMIFS(A8:A40,C8:C40,"X")

If you want it to sum non-empty cells instead, use:

=SUMIFS(A8:A40,C8:C40,"<>" & "")
where the operator is concatenated with "".

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Fri Oct 13, 2023 at 2:55 pm
Discuss

Discussion

I'm not sure why we in DK have to use ";" instead of "," and use "." in the SUM.IFS ?? The right working formular is: =SUM.IFS(A8:A40;C8:C40;"<>" & "")

thank for your help ;-)
magnificent (rep: 4) Oct 13, '23 at 3:34 pm
I don't know the Danish vetsion but glad you got it to work. Thanks for selecting my Answer. Morten.
John_Ru (rep: 6142) Oct 13, '23 at 3:44 pm
Add to Discussion
0

Hello Magnificent and welcome to the forum,

I am suprised the formula works since it is usually written SUMIFS without the dot/period after SUM. As written, it doesn't work for me so I can't test my suggestion.

The fix for you "not empty cell" check could be as simple as wrapping the criteria in quotes: "<>""" rather than <>"".

Cheers   :-)

posted by: WillieD24 (rep: 557) on Fri Oct 13, 2023 at 1:29 pm
Discuss

Discussion

I'm not sure why we in the danish version had to write SUM.HVISER (SUM.IFS in english), and use semicolons instead of comma - but this formular works: =SUM.HVISER(A8:A40;C8:C40;"<>" & "")
magnificent (rep: 4) Oct 13, '23 at 3:38 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login