countifs returns only zero

Hi Izzy,

Here is an example that uses Countifs. I don't think Countifs was available in 2003 Excel so you must have a newer version?

Countifs Example with GT 100.xlsx

I find the quote marks around the ">100" are somewhat confusing.

Izzy, Good evening.

Try to use:

Please Login or Register  to view this content.

Is that what you want?

I hope it helps.

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

This is what you should be using:

=SUMPRODUCT((Requisitions!C:C>100)*(Requisitions!B:B=A3))

NOT this:

=SUMPRODUCT((Requisitions!C:C>100)*(Requisitions!B:B='260218'!A3))

HOWEVER, it will not work unless the entries in column B of the requisitions sheet are exactly the same as the lookup value. Your column B contains, in many cases, multiple names and extra data like this: [C]

You will also need to change the fomula in C2 of the requisitions sheet, which is currently returning text, to this:

=IFERROR(VALUE(LEFT(A2, LEN(A2)-9)),"")

This will turn it into a number. Then this will start to work:

=COUNTIFS(Requisitions!C:C,">"&100,Requisitions!B:B,A2)

1. You do not need the text "days ago" in column A of the Requisition sheet, and therefore you don't need column C.
2. If you insist on the text in column A, then use CUSTOM FORMATTING with 0 " days ago";; 0 " days"

Named Range ReqTable:=Requisitions!$A$2:INDEX(Requisitions!$B:$B,COUNT(Requisitions!$A:$A)+1)
3. On 260218 sheet column B, use

Please Login or Register  to view this content.