SOLVED

EXCEL SUM.IF....

%3CLINGO-SUB%20id%3D%22lingo-sub-3012102%22%20slang%3D%22en-US%22%3EEXCEL%20SUM.IF....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012102%22%20slang%3D%22en-US%22%3E%3CP%3Ehi!%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20make%20a%20chat%20en%20the%20attaced%20excel%20that%20is%20not%20working.%3C%2FP%3E%3CP%3EThe%20problen%20is%20in%20%22T1%20Bilan%22%20page.%3C%2FP%3E%3CP%3Ecan%20someone%20tell%20me%20why%20data%20coming%20from%20page%20%22journal%22%20is%20not%20taking%20into%20consideration%20after%202014%3F%3C%2FP%3E%3CP%3EThanks%2C%20Juan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3012102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3012158%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20SUM.IF....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%2BF6%2BSUMIFS(journal!%24J%3A%24J%2Cjournal!%24G%3A%24G%2C%24D6%2Cjournal!%24F%3A%24F%2C%24B%242%2Cjournal!%24E%3A%24E%2C'T1%20Bilan'!%24G%243)-SUMIFS(journal!%24K%3A%24K%2Cjournal!%24G%3A%24G%2C%24D6%2Cjournal!%24F%3A%24F%2C%24B%242%2Cjournal!%24E%3A%24E%2C'T1%20Bilan'!%24G%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20have%20to%20add%20dollar%20signs%20in%20expressions%26nbsp%3Bjournal!E%3AE%20and%26nbsp%3B'T1%20Bilan'!G3%20(change%20to%26nbsp%3Bjournal!%24E%3A%24E%20and%20'T1%20Bilan'!%24G%243).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3012188%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20SUM.IF....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%2BF5%2BSUMIFS(journal!%24J%3A%24J%2Cjournal!%24G%3A%24G%2C%24D5%2Cjournal!%24F%3A%24F%2C%24B%242%2Cjournal!%24E%3A%24E%2C'T1%20Bilan'!G%243)-SUMIFS(journal!%24K%3A%24K%2Cjournal!%24G%3A%24G%2C%24D5%2Cjournal!%24F%3A%24F%2C%24B%242%2Cjournal!%24E%3A%24E%2C'T1%20Bilan'!G%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%26nbsp%3Bjournal!%24E%3A%24E%2C'T1%20Bilan'!G%243%20has%20to%20be%20entered%20instead%20of%26nbsp%3Bjournal!E%3AE%2C'T1%20Bilan'!G3.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3013303%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20SUM.IF....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3013303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20you%20were%20right%20about%20that%20missing%20%22%24%22.%20however%2C%20the%20following%20years%20the%20sum.if%20formula%20still%20does%20not%20work...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20Juan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3013954%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20SUM.IF....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3013954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20spreadsheet%20the%20numbers%20for%20the%20following%20years%20are%20calculated%20by%20sumifs%20formula%20as%20intended.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20when%20i%20opened%20your%20spreadsheet%20there%20was%20an%20error%20message%20that%20a%20named%20range%20was%20removed%20because%20it%20couldn't%20be%20read.%20Maybe%20this%20causes%20different%20results%20in%20your%20and%20my%20spreadsheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

hi!

I have been trying to make a chat en the attaced excel that is not working.

The problen is in "T1 Bilan" page.

can someone tell me why data coming from page "journal" is not taking into consideration after 2014?

Thanks, Juan

6 Replies

@juan jimenez 

=+F5+SUMIFS(journal!$J:$J,journal!$G:$G,$D5,journal!$F:$F,$B$2,journal!$E:$E,'T1 Bilan'!G$3)-SUMIFS(journal!$K:$K,journal!$G:$G,$D5,journal!$F:$F,$B$2,journal!$E:$E,'T1 Bilan'!G$3)

 

I guess journal!$E:$E,'T1 Bilan'!G$3 has to be entered instead of journal!E:E,'T1 Bilan'!G3.

@Quadruple_Pawn 

Thank you, you were right about that missing "$". however, the following years the sum.if formula still does not work...

 

Any suggestions?

 

Thank you, Juan

@juan jimenez 

In my spreadsheet the numbers for the following years are calculated by sumifs formula as intended.

 

However when i opened your spreadsheet there was an error message that a named range was removed because it couldn't be read. Maybe this causes different results in your and my spreadsheet.

best response confirmed by juan jimenez (Frequent Contributor)
Solution

@juan jimenez 

I didn't check entire formula, but in column G you have partly texts partly numbers. It shall be something one, depends on criteria used

image.png

Sergei, once again you helped a lot. Thank you very much.

I wonder if you could help me again. I am trying to save time with the procedure with have now. I will post in a different conversation so that i can upload docs.

@juan jimenez , glad it helped