Sumifs

[David02]

Hi, I am new to this forum and currently having some problems with SUMIFS. I am trying to get the formula to check if criteria 1 and 2 are met and then if they are add the sum range value to get a total. For criteria 1 I found that the formula in G3 only notices 124 if the cell contains a letter and so the value in G3 is currently 2 but the total should be 19. Does anyone know where I might be going wrong?[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Criteria 1 range[/TD]
[TD]Criteria 2 range[/TD]
[TD]Sum range[/TD]
[TD][/TD]
[TD][/TD]
[TD]Criteria 1[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]P124[/TD]
[TD]Company A[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Criteria 2[/TD]
[TD]Company A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1246903[/TD]
[TD]Company A[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]=SUMIFS(C2:C5,A2:A5,"*"&G1&"*",B2:B5,G2)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]521246[/TD]
[TD]Company A[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]124[/TD]
[TD]Company A[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sixthsense]

Try this..

=SUMPRODUCT(ISNUMBER(SEARCH(G1,A2:A5))*ISNUMBER(SEARCH(G2,B2:B5)),C2:C5)

 

[David02]

Just tried it and it worked. Thanks so much

 

[David02]

Would you know if it is difficult to get this to work as well for a set range of dates. For example I set a range of dates some within June and some not, within column D and then set criteria for the end of May and start of June.

[TABLE="width: 654"]
<colgroup><col span="3"><col><col span="5"><col></colgroup><tbody>[TR]
[TD]Criteria 1 range[/TD]
[TD]Criteria 2 range[/TD]
[TD]Sum range[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Criteria 1[/TD]
[TD="align: right"]124[/TD]
[TD][/TD]
[TD]Criteria 3[/TD]
[TD="align: right"]31/05/2019[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]Company A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]04/06/2019[/TD]
[TD][/TD]
[TD]Criteria 2[/TD]
[TD]Company A[/TD]
[TD][/TD]
[TD]Criteria 4[/TD]
[TD="align: right"]01/07/2019[/TD]
[/TR]
[TR]
[TD]1246903[/TD]
[TD]Company A[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20/05/2019[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]521246[/TD]
[TD]Company A[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]09/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]Company A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]02/07/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]