Counting formula with multiple entries in multiple columns
Hello everyone,
I have tried numerous iterations of SUMIF and COUNTIF and can't get around a pesky problem. Attached is a xls showing data that mirrors the actual xls I am working on. Each row in the Data Tab in the overall range of A3:O14 is one contract for the "fruits" in column A; so the row starting at A3 is the first contract for "apples", which has multiple payments of that contract (e.g. two of them in this case), and whose total contract amount is $5,000. On the Calc Tab, I am trying to add up the total number of contracts for each "fruit" for ONLY the green highlighted section of the Data Tab (i.e. for only the months of May, June, and July). The problem I am having is that the formulas I keep trying to use keep counting each column as a single "contract" when a single row is ONE contract. For example, "apples" has a total of 4 contracts all together (i.e. rows 3,6,9,13), but only 3 contracts in the green highlighted section because all of its contracts has a payment that is paid inside the green section EXCEPT for one--the contract in row 13 gets paid in October, outside the green section, so it does not count.
In the Calc Tab, I show you the results I am supposed to get (with the right formula) for each of the fruits.
The actual spreadsheet I need this for is many dozens of columns long and hundreds of rows deep, so I have been trying to put together array formulas to tackle the entire spreadsheet.
If more information is needed, please let me know; I am happy to explain further. Would greatly appreciate some help! Been at it for hours!
| Attachment | Size |
|---|---|
| FruitExample.xlsx | 10.09 KB |
I Tried playing about with
I Tried playing about with sumproduct because on reading that sounded like the ideal function. but that is counting the individual entries between H:J for apples so is showing as 5.
There is definitely a better way to do this but I can't think of it. So as a messy solution put the following in P3 and copy down:
=IF(COUNT(H3:J3)>0,A3,"")
and you'll get Apples or Grapes or whatever if they have any values in May - Jul. You can then use a PIVOT TABLE or alternately countif function as follows to total:
=countif(P:P,"Apples"), and change the fruit for each one you want a total for. PIVOT TABLE would be neater for this final step
Regards
Mark