SOLVED

Excel syntax for summing up values that satify 2 conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2874213%22%20slang%3D%22en-US%22%3EExcel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2874213%22%20slang%3D%22en-US%22%3EI've%20to%20sum%20up%20all%20values%20that%20are%20multiples%20of%20123%20and%20also%20came%20from%20xyz123%20source.%3CBR%20%2F%3EHow%20can%20I%20make%20that%20formula%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2874213%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2874339%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2874339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193208%22%20target%3D%22_blank%22%3E%40Elhilayla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20actually%20guess%20what%20is%20meant%20with%20source%20xyz123.%3C%2FP%3E%3CP%3EBut%20i%20can%20suggest%20a%20formula%20to%20add%20all%20numbers%20that%20are%20multiples%20of%20123%20and%20that%20are%20in%20range%20A3%3AA6.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(MOD(A3%3AA6%2C123)%3D0%2CA3%3AA6))%3C%2FP%3E%3CP%3EThe%20formula%20has%20to%20be%20entered%20as%20arrayformula%20with%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2874797%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2874797%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%3EVery%20much%20appreciated%20my%20friend%3C%2FP%3E%3CP%3ESorry%20for%20the%20confusion%26gt%3B%3C%2FP%3E%3CP%3EI%20mean%20to%20say%20that%20the%20inputs%20come%20from%20different%20sources%2C%20what%20is%20required%20is%20a%20specific%20source%26gt%3B%3C%2FP%3E%3CP%3EFor%20instance%2C%2020%2F07%2F2021%20source%20is%26nbsp%3B%3CU%3E%3CSTRONG%3ERecept%20-%20VFC%20***tdG%26nbsp%3B%20%26nbsp%3B%3C%2FSTRONG%3E%3C%2FU%3Eit%20is%20a%20multiple%20of%20123%3C%2FP%3E%3CTABLE%20width%3D%22630px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F16%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3EEnvoi%20-%20VFC%20***bTz%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E2077.5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F16%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3ERecept%20-%20VFC%20***86m%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E450.03%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E2527.53%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F19%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3EROuioRS%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20***%26nbsp%3B%26nbsp%3B%20_V%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E2225.54%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F19%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3EMXU%20MykCHE%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20_F%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E2216.58%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F19%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3EYZUAN%20MerCHE%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20_F%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E2166.09%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F20%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3ERecept%20-%20VFC%20***tdG%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E1230%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E3398.06%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22141px%22%3E7%2F21%2F2021%3C%2FTD%3E%3CTD%20width%3D%22269px%22%3ESEP%20A%3A%26nbsp%3B%26nbsp%3B%2005306449863%3C%2FTD%3E%3CTD%20width%3D%22109px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22111px%22%3E3396.56%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2874952%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2874952%22%20slang%3D%22en-US%22%3Eso%2C%20we%20need%20ifs%2C%20to%20satisfy%20these%202%20conditions%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875075%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875075%22%20slang%3D%22en-US%22%3EI%20came%20up%20with%20this%2C%20but%20no%20result%20i.e.%200%3CBR%20%2F%3E%3DSUMIFS(D2%3AD292%2CB2%3AB292%2C%22Recept%20-%20VFC***%22%2CD2%3AD292%2C%22MOD(%24D%242%3A%24D%24292%2C123)%3D0%22)%3CBR%20%2F%3Ewould%20you%20Kindly%20correct%20my%20SYNTAX.%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875168%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193208%22%20target%3D%22_blank%22%3E%40Elhilayla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20sample%20I%20see%20no%20one%20number%20in%20column%20D%20which%20is%20multiply%20of%20123.%20Are%20such%20in%20actual%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875193%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875193%22%20slang%3D%22en-US%22%3EThere's%20one%3CBR%20%2F%3E1230%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875204%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875204%22%20slang%3D%22en-US%22%3EWould%20you%20kindly%20correct%20my%20syntax%20above.%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875270%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193208%22%20target%3D%22_blank%22%3E%40Elhilayla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20only%20sum%20up%20values%20by%20adding%202%20helper%20columns%20and%20by%20restricting%20the%20partial%20match%20search%20to%26nbsp%3B%22Recept%20-%20VFC%22%20(instead%20of%26nbsp%3B%22Recept%20-%20VFC***%22).%3C%2FP%3E%3CP%3EI%20added%20helper%20columns%20E%20and%20F%20with%20formulas%3C%2FP%3E%3CP%3E%3DIFERROR(MOD(C2%2C123)%2C%22%22)%3C%2FP%3E%3CP%3Eand%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNT(FIND(%22Recept%20-%20VFC%22%2CB2))%3C%2FP%3E%3CP%3EThen%20i%20can%20calculate%20the%20sum%20with%20formula%3C%2FP%3E%3CP%3E%3DSUMIFS(D2%3AD292%2CE2%3AE292%2C%220%22%2CF2%3AF292%2C%221%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20i%20copied%20the%20data%20into%20Excel%2C%20the%20numbers%202077.5%26nbsp%3B%202527.53%202225.54%20and%20so%20on%20were%20entered%20as%20text.%20I%20had%20to%20convert%20them%20to%20numbers%20with%20the%26nbsp%3B%3CSPAN%3ENUMBERVALUE%26nbsp%3B%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875271%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193208%22%20target%3D%22_blank%22%3E%40Elhilayla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20that%20I%20need%20to%20understand%20where%20is%20which%20column.%20If%20dates%20are%20in%20column%20A%2C%20next%20is%20B%2C%20when%201230%20is%20in%20column%20C%2C%20not%20in%20column%20D.%20Perhaps%20you%20may%20provide%20sample%20file%20instead%20of%20copy%2Fpasting%20info%20into%20the%20body%20of%20the%20post%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875323%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193208%22%20target%3D%22_blank%22%3E%40Elhilayla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20583px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319340iF50DBDBEB0A69F83%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875482%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875482%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875487%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20syntax%20for%20summing%20up%20values%20that%20satify%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875487%22%20slang%3D%22en-US%22%3EThanks%2C%20QP%20%26amp%3B%20Sergei%3CBR%20%2F%3Efor%20the%20interest%20in%20the%20discussion%2C%20I'm%20attaching%20the%20full%20file.%3CBR%20%2F%3EHere%20the%20MOD%20is%20110%2C%20other%20conditions%20remain%3C%2FLINGO-BODY%3E
Occasional Contributor

I've to sum up all values that are multiples of 123 and also came from the source on column B "xyz123".

I should come up with IFS to satisfy 2 conditions i.e. 2 IFS
How can I make that formula 

19 Replies

@Elhilayla 

I can't actually guess what is meant with source xyz123.

But i can suggest a formula to add all numbers that are multiples of 123 and that are in range A3:A6. 

=SUM(IF(MOD(A3:A6,123)=0,A3:A6))

The formula has to be entered as arrayformula with ctrl+shift+enter.

@Quadruple_Pawn 

Very much appreciated my friend

Sorry for the confusion>

I mean to say that the inputs come from different sources, what is required is a specific source>

For instance, 20/07/2021 source is Recept - VFC ***tdG   it is a multiple of 123

7/16/2021Envoi - VFC ***bTz   2077.5
7/16/2021Recept - VFC ***86m 450.032527.53
7/19/2021ROuioRS      ***   _V 2225.54
7/19/2021MXU MykCHE     _F 2216.58
7/19/2021YZUAN MerCHE     _F 2166.09
7/20/2021Recept - VFC ***tdG 12303398.06
7/21/2021SEP A:   05306449863 3396.56
so, we need ifs, to satisfy these 2 conditions
I came up with this, but no result i.e. 0
=SUMIFS(D2:D292,B2:B292,"Recept - VFC***",D2:D292,"MOD($D$2:$D$292,123)=0")
would you Kindly correct my SYNTAX.
Thanks

@Elhilayla 

In sample I see no one number in column D which is multiply of 123. Are such in actual data?

Would you kindly correct my syntax above.
Thanks

@Elhilayla 

I can only sum up values by adding 2 helper columns and by restricting the partial match search to "Recept - VFC" (instead of "Recept - VFC***").

I added helper columns E and F with formulas

=IFERROR(MOD(C2,123),"")

and 

=COUNT(FIND("Recept - VFC",B2))

Then i can calculate the sum with formula

=SUMIFS(D2:D292,E2:E292,"0",F2:F292,"1")

 

When i copied the data into Excel, the numbers 2077.5  2527.53 2225.54 and so on were entered as text. I had to convert them to numbers with the NUMBERVALUE  formula.

@Elhilayla 

For that I need to understand where is which column. If dates are in column A, next is B, when 1230 is in column C, not in column D. Perhaps you may provide sample file instead of copy/pasting info into the body of the post?

@Elhilayla 

Perhaps you mean

image.png

Thanks, QP & Sergei
for the interest in the discussion, I'm attaching the full file.
Here the MOD is 110, other conditions remain
Below is the full file as you requested.
MOD is 110, as I mentioned earlier.

@Elhilayla 

Actually it's the same, I only saved the file in Excel format to keep formulas and formatting, transformed source data to structured table (Ctrl+T) and use named parameters:

image.png

Thanks Sergei
That's awesome.
Would you kindly try to correct my formula, withs IFS syntax?
Thanks
best response confirmed by allyreckerman (Microsoft)
Solution

@Elhilayla 

Afraid SUMIFS() doesn't work in such case, you can't use other functions within criteria. Alternatively you may add helper column to your data which returns TRUE or FALSE (or whatever your prefer) if base number is multiplier for values in another column or not. After that use that helper column in SUMIFS().

I truly appreciate
Thaks
Very much appreciated.
Thanks Herbert