Sumif vs Sumproduct

[jlkirk]

In column A I have Account Names (A,B,C); they are listed randomly in rows 1-100. In column B I have dollar amounts (different for each account listed in column A). In each cell in column C I have text that includes the words "SALES" and "SHRS". In each cell in column D there is either an "S" or an "L".

What I would like to do in column F is insert the dollar amounts in column B when (i) the cell in column C contains the words "SALES" and "SHRS", and (ii) the cell in column D contains an "L"

Therefore, for example, if B1 was $100, C1 contained "SALES" and "SHRS" (among other verbiage), and cell D1 contained an "L", then F1 would be populated with $100; if, continuing the example, if D1 contained an "S", cell F1 would be blank or empty.

Any help?

I think its a sumproduct issue, but cant figure out how to use the wildcards.

 

[jlkirk]

you could use

jlkirk​

not sure how the sumif() type of formula worked
but would need a SUMIFS() - i suspect a type

SUMIFS()

=sumifs(B1,C1,"*SALES*",C1,"*SHRS*",D1,"L")
and add a date range for Z


BUT do you now what to add up a range and NOT just 1 cell

=SUMIFS(B1,C1,"*SALES*",C1,"*SHRS*",D1,"L",Z1,">="&DATEVALUE("1/1/24"),Z1,"<="&DATEVALUE("31/1/24"))

Good point. In your first response, "=sumifs(B1,C1,"SALES",C1,"*SHRS*"<D1,"L"), how do you add the date range if I want to the data in the cell only for a day if it is during January? For example, could I add data in column AA of months, "January" for example, and the date range look at the month of the transaction, column Z, and enter data only when the month of the day equals the month in AA?

 

[etaf]

yes - BUT that will not take into account the year

=IF(AND( MONTH(Z1)=1,D1="L",ISNUMBER(SEARCH("sales",C1,1)),ISNUMBER(SEARCH("SHRS",C1,1))),B1,"")

and
=IF(AND( MONTH(Z1)=MONTH(AA1),D1="L",ISNUMBER(SEARCH("sales",C1,1)),ISNUMBER(SEARCH("SHRS",C1,1))),B1,"")

Book1
	A	B	C	D	E																			X	Y	Z	AA
1	12	12	sales SHRS	L																						1/2/24	1/12/24
Sheet1

Cell Formulas
Range	Formula
A1	A1	=IF(AND( MONTH(Z1)=MONTH(AA1),D1="L",ISNUMBER(SEARCH("sales",C1,1)),ISNUMBER(SEARCH("SHRS",C1,1))),B1,"")