SUMIFS condition not working

Hi

I am using a SUMIFs formual to calculate three different types of conditions and referencing them to another sheet.

The formula is: =SUMIFS('All Segments'!D:D,'All Segments'!$N:$N,"Mainspreadsheet!"&B2,'All Segments'!$P:$P,"*"&Mainspreadsheet!C2&"*",'All Segments'!$C:$C,Mainspreadsheet!D2)

Everything works until I add in ",'All Segments'!$N:$N,"Mainspreadsheet!"&B2,'" which is the 3rd condition. Can someone provide a fix for the formula as I am not sure how to fix this particular one. Apologies for the formula not being clean as well, I am an excel newbie.

Thanks

Try
The formula is: =SUMIFS('All Segments'!D:D,'All Segments'!$N:$N,Mainspreadsheet!B2,'All Segments'!$P:$P,"*"&Mainspreadsheet!C2&"*",'All Segments'!$C:$C,Mainspreadsheet!D2)

Thanks for the help Sandy and Jonmo1

So i took the suggestion and got rid of the & so now its: =SUMIFS('All Segments'!D:D,'All Segments'!$N:$N,Mainspreadsheet!B2,'All Segments'!$P:$P,"*"&Mainspreadsheet!C2&"*",'All Segments'!$C:$C,Mainspreadsheet!D2)

It still displays a result of 0 when it should be 433. I actually added the "&" in the first place after reading some earlier posts by someone who suggested putting &. Any other suggestions?

Attach a sample workbook. (Not a picture!)
Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
Remember to desensitize the data.
Note:

• Please do not attach password protected workbooks/worksheets
• Please do not attach file(s) from exterior servers
• Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

20 rows of data is enough (probably)

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

Thanks for the reply. I will try to get a sample workbook when I get home, I have to run off for a few hours, thanks again!

Hi

Ironically enough, as I was making the mock workbook (as attached), the equation actually works.

I did however change the ranges around for easier reading (E.g. A - A, B- B, C - C compared to my previous workbook which was N-B, P-C and C-D). Is that the reason why it's not working? Something to do with the ranges?

Thanks

Thanks for the spreadsheet. I manage to fix the problem but I still don't get why.

So basically I had the word 'business' in one of my columns in the sheet 'All Segments'. In my 'Mainspreadsheet' sheet, i had the condition text 'business' as well but it would still give me 0. I then tried copy and pasting the 'business' text from the 'All Segments' Column into my 'Mainspreadsheet' one and it worked. I don't get it though, they both are the exact same in terms of text? Can you explain why?

Seems the basic problem is solved so
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks

One of the 'busines' texts did not exactly equal 'business'. It probably had a leading or trailing space.
The same thing that caused you to do this in your original formula
"*"&Mainspreadsheet!C2&"*"

But it ("*"&Mainspreadsheet!C2&"*" ) should recognize business with or without spaces

@sandy666
Right, but that's not where the problem is.
According to the original post, the problem is in the N column, where the original incorrectly did the concatenation
'All Segments'!$N:$N,"Mainspreadsheet!"&B2

But after correcting that concatenation to
'All Segments'!$N:$N,Mainspreadsheet!B2

It was still returning 0.


So maybe, the original formula needs to do the wildcards on both column criteria.
=SUMIFS('All Segments'!D:D,'All Segments'!$N:$N,"*"&Mainspreadsheet!B2&"*",'All Segments'!$P:$P,"*"&Mainspreadsheet!C2&"*",'All Segments'!$C:$C,Mainspreadsheet!D2)

You are right but without example with original data it's hard to say anything especially with words: maybe, probably, etc
I've solution: No see - No idea