Oct 22 2021 07:57 AM - edited Oct 22 2021 11:34 AM
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
Oct 22 2021 08:21 AM
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.
Oct 22 2021 10:15 AM
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 |
Oct 22 2021 11:09 AM
Oct 22 2021 11:30 AM
Oct 22 2021 11:56 AM
In sample I see no one number in column D which is multiply of 123. Are such in actual data?
Oct 22 2021 12:08 PM
Oct 22 2021 12:09 PM
Oct 22 2021 12:24 PM
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.
Oct 22 2021 12:24 PM
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?
Oct 22 2021 12:41 PM
Oct 22 2021 01:28 PM
Oct 22 2021 01:29 PM
Oct 22 2021 01:33 PM
Oct 22 2021 02:46 PM
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:
Oct 23 2021 03:58 AM
Oct 23 2021 04:53 AM
SolutionAfraid 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().
Oct 23 2021 11:44 AM
Oct 23 2021 02:44 PM
@Elhilayla , you are welcome
Oct 26 2021 07:12 AM
Oct 23 2021 04:53 AM
SolutionAfraid 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().