 SOLVED

Excel syntax for summing up values that satify 2 conditions

Occasional Contributor

Excel syntax for summing up values that satify 2 conditions

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

Re: Excel syntax for summing up values that satify 2 conditions

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.

Re: Excel syntax for summing up values that satify 2 conditions

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/2021 Envoi - VFC ***bTz 2077.5 7/16/2021 Recept - VFC ***86m 450.03 2527.53 7/19/2021 ROuioRS      ***   _V 2225.54 7/19/2021 MXU MykCHE     _F 2216.58 7/19/2021 YZUAN MerCHE     _F 2166.09 7/20/2021 Recept - VFC ***tdG 1230 3398.06 7/21/2021 SEP A:   05306449863 3396.56

Re: Excel syntax for summing up values that satify 2 conditions

so, we need ifs, to satisfy these 2 conditions

Re: Excel syntax for summing up values that satify 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

Re: Excel syntax for summing up values that satify 2 conditions

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

There's one
1230

Re: Excel syntax for summing up values that satify 2 conditions

Would you kindly correct my syntax above.
Thanks

Re: Excel syntax for summing up values that satify 2 conditions

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.

Re: Excel syntax for summing up values that satify 2 conditions

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?

Re: Excel syntax for summing up values that satify 2 conditions

Perhaps you mean Re: Excel syntax for summing up values that satify 2 conditions

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

Re: Excel syntax for summing up values that satify 2 conditions

Below is the full file as you requested.
MOD is 110, as I mentioned earlier.

Re: Excel syntax for summing up values that satify 2 conditions

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: Re: Excel syntax for summing up values that satify 2 conditions

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

Re: Excel syntax for summing up values that satify 2 conditions

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().

Re: Excel syntax for summing up values that satify 2 conditions

I truly appreciate
Thaks

Re: Excel syntax for summing up values that satify 2 conditions

@Elhilayla , you are welcome

Re: Excel syntax for summing up values that satify 2 conditions

Very much appreciated.
Thanks Herbert