If your data are in the range A2:D25, then you could use the following array formula:
=SUM(($A$2:$A$25=DATEVALUE("8/11/13"))*
(($B$2:$B$25="oranges")+($C$2:$C$25="oranges")+($D$2:$D$25="oranges")))
You will need to enter it with the Control-Shift-Enter key combination.
If you have several dates for which you want to calculate the totals for each of a large number of products, you could do the following.
Let's assume that your product data are in the range A2:D20000 in Sheet 1, with the date range in column A, as in your example. Say you want to put the product counts in Sheet2.
In Sheet2, you would enter into cells B1, C1, etc., the dates you are interested in. Below that, in the range A2:A2000, you have the list of your products. In cell B2, enter this array formula:
=SUM((Sheet1!$A$2:$A$20000=B$1)*((Sheet1!$B$2:$B$20000=$A2)+
(Sheet1!$C$2:$C$20000=$A2)+(Sheet1!$D$2:$D$20000=$A2)))
and copy it down to the bottom of the list of products. That gives you the counts for the first date.
Next, copy that whole column of product counts across for each column that has a date in row 1. That gives you the counts for all the dates.
Alternatively, if you have both a large number of products and a large number of dates, an only slightly more complicated non-array formula will give you better performance (i.e., will take less time to calculate).
In cell B2, you would instead enter:
=COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$B$2:$B$20000,$A2)+
COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$C$2:$C$20000,$A2)+
COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$D$2:$D$20000,$A2)
Then, you'd copy the formula down to the bottom of the products list, and then copy that whole column of formulas (in the range B2:B2000) across all the columns for which you've put dates in row 1.
The thing is I have many (thousands) of products (e.g. "oranges"), so I can't just go and create such an equation for each one of them – lribinik – 2013-08-11T15:32:58.630
OK, but what if I have many dates (around 100)? – lribinik – 2013-08-12T05:05:21.413
Enter them into row 1 and then copy the formulas across for each date column. Considering the number of products and dates you need to deal with, I've added a non-array formula approach to my answer. You would enter it in the same initial cell as the array formula, but confirming it with just the Enter key instead of the Control-Shift-Enter key combination. Then copy down and across as before. – chuff – 2013-08-12T05:57:14.310