SUMPRODUCT/SUMIFS

[barbagan]

I need formula to add up values that fulfil criteria on non-parallel references (i.e. a row based criteria and column based criteria in an array at the same time. For example, how many bananas and apples in boxes numbered between 3 and 7, given the following data, would give 56 (from 10+11+31+4) as a result :


Box Size>>> 1 2 3 5 8 9


bananas 20 15 10 11 32 0

apples 8 8 31 4 12 7


It seems neither SUMPRODUCT nor SUMIF are any use when the criteria data is not in parallel arrays.


Thanks for your help, and Best Wishes for 2012.

 

[Hui]

Barbagan


Firstly, Welcome to the Chandoo.org Forums


Oh how incorrect you are, Sumproduct is perfect at just that


Have a read of: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[barbagan]

How right YOU are ..... I'll sink my teeth into that a.s.a.p. Looks like what I was looking for.Thanks a lot.

 

[barbagan]

Hi Hui


It's me again; got ahead with that SUMPRODUCT issue just fine. But I've just unearthed a problem with COUNTIF.


I want to produce an 7 row x 7 column array where a formula in the 49 cells pairs the numbers 1 to 7 and shows the result, but if the result is a duplicate (e.g. 11, 22 and so on) it shows 0 or "". I then count how many pairs I have (which should always be 42 of course). I am finding that COUNTIF fails to produce the correct result when the COUNTIF criterion is set at ">"&0. Same thing happens if the criterion in the COUNTIF cell is set as "<>"&"" and the 'fail' value in the array cells' formula is set to "" to produce blank in the cells where the result is a duplicate pair.


It only works if the COUNTIF criterion is set to "<>"&0, or if I use SUMPRODUCT with a >0 filter. BTW, the array numbers are formatted as such; and formatting them to General does not make a diff.


So why is COUNTIF behaving inconsistently?


Thanks for your help.


To sum up:

 

[Hui]

Can you post the formula you have on the 7x7 area? And the countif formula ?

 

[barbagan]

In B1:H7, and in A2:A8, enter 1,2,3,4,5,6,7 where


Formula in B2, =IF(B$1<>$A2,B$1&$A2,0); copy/paste across B2:H8.

This produces an array of 42 values, min 12, max 76, and 9 zeroes on the diagonal B2, C3,...,H8.


Then, =COUNTIF(B2:H8,">"&0) produces 0!


But =SUMPRODUCT(--(B2:H8>0)) gives 42

...and =COUNTIF(B2:H8,"<>"&0) gives 42.


Thanks

 

[NARAYANK991]

Hi ,


The formula B$1&$A2 , which gives 12 from 1 and 2 is not the number 12. To check , just enter the formula :


=B$1&$A2=12


where B1 = 1 and A2 = 2. You should get FALSE.


Narayan

 

[barbagan]

Well spotted NARAYANK991. So I've now changed the formula to convert all 49 cell results to a number, that is


=IF(B$1<>$A2,VALUE(B$1&$A2),0)


Previously, I thought that formatting the array as Numbers would do just that, but obviously it was not.


Thanks, and Best Wishes for 2012 BTW.

 

[NARAYANK991]

Hi ,


Thanks. Wish you a very Happy New Year ; all the best for 2012.


Narayan