sumifs - criteria is blank/empty

[ajm]

folks, when the number of invoices rejected each month, i generate a report that states the total number of rejections, and the count of each of the possible rejection reasons split out by week. Most reasons are pretty obvious: Cust ID not found, Cust under suspension, etc. to do this, I use a table with all the reasons listed in column C.

Raw data is listed on the Rejections tab:

Formula is

SUMIFS('Rejections'!$C:$C,'Rejections'!$E:$E,">"&"="&Invoices!CZ$8,'Rejections'!$E:$E,"<"&Invoices!DA$8,'Rejections'!$A:$A,Invoices!$C$2,'Rejections'!$V:$V,Invoices!$C53,'Rejections'!$AF:$AF,"<>""0""")

Invoices!CZ$8 and Invoices!DA$8 contain dates
Invoices!$C$2 contains a division name

Column C on the invoices tab contains a list of reasons the invoice may be rejected. these correspond to the reason given on each invoice listed on the Rejections tab. The last reason in the list is actually blank which is for those here who reject the invoice but do not include a reason. Aside from manually filling in the blanks with "Blank" and listing "Blank" as a reason, can anyone suggest another way to capture these?

 

[Marcelo Branco]

I'm not sure i understand what you trying to do.

To count the different reasons listed in column C i think you should use COUNTIFS, not SUMIFS.
For example, to count "Cust ID not found"
=COUNTIFS('Rejections'!$C:$C,"Cust ID not found",...)

Another issue is the last condition of your formula
Rejections'!$AF:$AF,"<>""0""")

Do you really have values in the AF column such as "0" (zero surrounded by quotes)? I'm asking because a condition to exclude values equal 0 in column AF should be:
Rejections'!$AF:$AF,"<>0")

M.

 

[Aladin Akyurek]

Hard to follow. Do you mean that column AF must be tested for being blank?

 

[ajm]

sorry folks. got the zero part wrong for column AF. its now "<>0" as it should be. also, using sumifs instead of countifs as some of the invoice numbers are entered twice in the data set i am using. I use column C on the Rejections tab to count only the first instance of each invoice number, all subsequent instances get a zero. hence, sumifs.

Aladin, I am displaying the reasons invoices are rejected and how often each reason is used. In this data set, while there is not always a reason input for rejecting an invoice, the $$ value of a rejected invoice will always show in column AF. So, I want all records that have an amount in column AF.

the issue I am having surrounds the capturing of the Blank reason.

SUMIFS('Rejections'!$C:$C,'Rejections'!$E:$E,">"&"="&Invoices!CZ$8,'Rejections'!$E:$E,"<"&Invoices!DA$8,'Rejections'!$A:$A,Invoices!$C$2,'Rejections'!$V:$V,Invoices!$C53,'Rejections'!$AF:$AF,"<>0")

Rejections tab contains a list of all invoices that have been submitted through a particular portal.

Rejections!$C:$C contains a 1 or 0 depending on whether an invoice number is repeated
Rejections!$E:$E contains the invoice date
Rejections!$A:$A contains the division
Rejections!$V:$V contains the reason for rejecting an invoice
Rejections!$AF:$AF contains an amount only if the invoice has been rejected

The invoices tab contains a table with weeks across the top and rejection reasons down the left hand side. Its used as a way of explaining why invoices are rejected

so, using my formula from above (with the AF criteria fixed), on the Invoices tab, i want to get the quantity of invoices rejected for each of the listed reasons. The last reason in the list is a blank or an empty cell. against this, i wish to display the number of times that invoices have been rejected without inputting a reason.

 

[Aladin Akyurek]

Please try to describe in words what is needed (with reference to any formula).

What is the range which houses the invoice numbers?

It seems you want a count of rejected invoices. Rejection reasons are given apparently in column V.

When V is blank, we still have a rejection. Column AF contains the corresponding $ value.

Must invoice dates and divisions also used for the above count?

 

[ajm]

Please try to describe in words what is needed (with reference to any formula).

What is the range which houses the invoice numbers?

It seems you want a count of rejected invoices. Rejection reasons are given apparently in column V.

When V is blank, we still have a rejection. Column AF contains the corresponding $ value.

Must invoice dates and divisions also used for the above count?

I think I have resolved it. Will use a countifs formula that looks for "" as the criteria.

Thanks for persevering with the initial query Aladin. Its moments like this when trying to explain a problem that I am certain I made the right choice to NOT be a teacher.