Sumifs formula that ignores text and blanks in the criteria column

[sshirazi]

Need the correct SUMIFS formula (due to multiple conditions) that ignores both text and blanks in the criteria column. Tried sum product but that does not work. Please help!

 

[markmzz]

Need the correct SUMIFS formula (due to multiple conditions) that ignores both text and blanks in the criteria column. Tried sum product but that does not work. Please help!

Hi!

Try this in D2:

=SUMPRODUCT(--(A2:A16<>""),--NOT(ISTEXT(A2:A16)),B2:B16)

	A	B	C	D	E
1	Col01	Col02		Result
2		352		3911
3	633	873
4		318
5		156
6	580	103
7	589	503
8		635
9	Text	382
10	555	310
11	222	275
12		892
13	885	584
14	Text	618
15	296	835
16	540	428
17
***	******	******	**	******	**

<tbody>
</tbody>

Markmzz

 

[Aladin Akyurek]

Care to post the SUMIFS formula that needs editing?

 

[sshirazi]

Hi!

Try this in D2:

=SUMPRODUCT(--(A2:A16<>""),--NOT(ISTEXT(A2:A16)),B2:B16)

	A	B	C	D	E
1	Col01	Col02		Result
2		352		3911
3	633	873
4		318
5		156
6	580	103
7	589	503
8		635
9	Text	382
10	555	310
11	222	275
12		892
13	885	584
14	Text	618
15	296	835
16	540	428
17
***	******	******	**	******	**

<tbody>
</tbody>

Markmzz

Great! now if column 1 has dates & I need to sum for a specific month, what would be the formula.
Thanks so much.