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
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Vendor[/TD]
[TD]Contract #[/TD]
[TD]TO#[/TD]
[TD]Invoice # [/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]1135[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1135[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1135[/TD]
[TD]7[/TD]
[TD]100[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1135[/TD]
[TD]5[/TD]
[TD]105[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1222[/TD]
[TD]1[/TD]
[TD]105[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]750[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]780[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]780[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]760[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]780[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]555[/TD]
[/TR]
</tbody>[/TABLE]


I have another report that has:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Row[/TD]
[TD]Vendor[/TD]
[TD]Contract #[/TD]
[TD]TO#[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ABC[/TD]
[TD]1135[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]1135[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]1135[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC[/TD]
[TD]1222[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABC[/TD]
[TD]750[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC[/TD]
[TD]780[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.