Forum Discussion
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().
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.
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 |
- 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.
ThanksI 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.