sumproduct

[tleehh]

using this formula
=SUMPRODUCT(H18:H103,--(TEXT(B18:B103,"yyyymm")=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+0,1),"yyyymm")))
when column E is mark with "X", not to add the values in column H.

 

[Bernard Liengme]

Please tell us what you are trying to do.
How does column E enter into this?
best wsihes

 

[T. Valko]

TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+0,1),"yyyymm")

That can be reduced to:

TEXT(NOW(),"yyyymm")

Try this...

=SUMPRODUCT(H18:H103,--(TEXT(B18:B103,"yyyymm")=TEXT(NOW(),"yyyymm")),--(E18:E103<>"X"))

 

[David Biddulph]

It isn't clear what question you are asking, but it also isn't clear why you
have the +0 in MONTH(TODAY())+0.
Isn't that the same as MONTH(TODAY()) ?
And isn't it therefore the same as saying
=SUMPRODUCT(H18:H103,--(TEXT(B18:B103,"yyyymm")=TEXT(TODAY(),"yyyymm")))

If you are asking how to exclude rows where column E says "X", try
=SUMPRODUCT(H18:H103,--(TEXT(B18:B103,"yyyymm")=TEXT(TODAY(),"yyyymm")),--(E18:E103<>"X"))--David Biddulph"tleehh" <[email protected]> wrote in messageusing this formula>=SUMPRODUCT(H18:H103,--(TEXT(B18:B103,"yyyymm")=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+0,1),"yyyymm")))> when column E is mark with "X", not to add the values in column H.