Sumifs Criteria Question

[southern5footer]

I use sumifs very regularly and have recently came across a situation that one of the criteria used will in some cases be blank and some case have data. Is there a way to build into your sumif ignore if isblank?

 

[Amanda Wilso]

Instead of leaving the criteria cell blank, enter a * sign. This will include all rows for that criterion.

 

[etaf]

can you be more specific with an example

=SUMIFS(C1:C4,A1:A4,"a",B1:B4,"<>")
this will add up the values in C if A =a and B is NOT blank

 

[Scott Huish]

Also with a regular SUMIF since you mentioned both:

=SUMIF(B1:B4,"<>",C1:C4)

This would sum the values in C1:C4 where B1:B4 wasn't blank.

 

[southern5footer]

I have a dataset that tracks invoices that has the following information (simplified example):

Table Name: INVOICEDATA

Vendor	Contract #	TO#	Invoice #	Amount
ABC	1135	1	100	200
ABC	1135	1	101	700
ABC	1135	7	100	300
ABC	1135	5	105	500
ABC	1222	1	105	1000
ABC	750		10	75
ABC	780		15	80
ABC	780		20	760
ABC	780		25	555

<tbody>
</tbody>

I have another report that has:

	A	B	C	D
Row	Vendor	Contract #	TO#	Total
1	ABC	1135	1
2	ABC	1135	5
3	ABC	1135	7
4	ABC	1222	1
5	ABC	750
6	ABC	780

<tbody>
</tbody>

For each of the total line I would have:

=sumif(INVOICEDATA[Amount],INVOICEDATA[Vendor],A1,INVOICEDATA[Contract #], B1, INVOICEDATA[TO#], C1)

Row 1-4 work fine but 5 and 6 don't.

 

[Dave Patton]

Excel 2010
	A	B	C	D
1	Vendor	Contract #	TO#	Total
2	ABC	1135	1	900
3	ABC	1135	5	500
4	ABC	1135	7	300
5	ABC	1222	1	1000
6	ABC	750		75
7	ABC	780		1395
8
2dd

Cell Formulas
Range	Formula
D2	=SUMIFS(InvoicedData[[#All],[Amount]],InvoicedData[[#All],[Vendor]],A2,InvoicedData[[#All],[Contract '#]], B2, InvoicedData[[#All],[TO'#]],"="&C2)

The Source information is a Table.
The formula is a SumifS.

 

[southern5footer]

Thank you so much! This just made my life so much easier.