{"id":514,"date":"2012-01-31T13:54:39","date_gmt":"2012-01-31T13:54:39","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=514"},"modified":"2023-12-30T23:12:18","modified_gmt":"2023-12-30T23:12:18","slug":"using-countifs-with-excel-2003","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/using-countifs-with-excel-2003\/","title":{"rendered":"Using COUNTIFS with Excel 2003"},"content":{"rendered":"

Excel 2007 introduced the function COUNTIFS to count cells based on multiple criteria.<\/p>\n

For example suppose you wish to count how many times the client Fowler\u00a0buys more than 250 shares. The answer for tha data\u00a0below\u00a0turns out to be\u00a02 using the Countifs function\u00a0entered in B13.<\/p>\n

Similarly for\u00a0Owen the\u00a0result\u00a0turns out to\u00a0be 1.\u00a0<\/p>\n

\u00a0\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Even though\u00a0COUNTIFS is not availailable\u00a0with Excel 2003 there is an alternative way to perform the same calculation using the SUMPRODUCT array formula.<\/p>\n

The formula<\/p>\n

\u00a0=SUMPRODUCT(–(A2:A11=”Fowler”),–(C2:C11>=250))<\/p>\n

typed into C13 produces the same result. The mysterious looking — operator calculates if true or false for each of the cells in the range A2:A11 returning 1 if true and 0 if false. The same goes for the range C2:C11 and the Sumproduct array function sums all the true, true (1*1)\u00a0combinations.<\/p>\n

Note – To show how —\u00a0works try typing –(A2:A11=”Fowler”) into a cell followed by Ctrl+Shift+Enter. This convets\u00a0the formula\u00a0into\u00a0the array formula {=(A2:A11=”Fowler”)}<\/p>\n","protected":false},"excerpt":{"rendered":"

Excel 2007 introduced the function COUNTIFS to count cells based on multiple criteria. For example suppose you wish to count how many times the client Fowler\u00a0buys more than 250 shares. The answer for tha data\u00a0below\u00a0turns out to be\u00a02 using the Countifs function\u00a0entered in B13. Similarly for\u00a0Owen the\u00a0result\u00a0turns out to\u00a0be 1.\u00a0 \u00a0         […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[44,119,201,340],"class_list":["post-514","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-advanced-excel-training","tag-countifs-in-excel-2003","tag-excel-functions","tag-london-training"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/514","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=514"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/514\/revisions"}],"predecessor-version":[{"id":6738,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/514\/revisions\/6738"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=514"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=514"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=514"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}