sumif columns only using rows as criterias

[melvin]

Gday all,

I thought this was an easy one . I used to use sum product where I required summing columns intersecting with rows.

This example I have to use rows as a criteria however get the sum of data within the column.

File attached. Appreciate any assistance.

Melv

 

[Ramesh]

Here you go.. Please find the attached updated file...

I could sum up the row & columns using the Array formula

Hope it helps!!

 

[melvin]

Perfecto mate. Would sumproduct ever work for this example ?


Cheers champ.

Melv.

 

[Khalid NGO]

Would sumproduct ever work for this example ?

Hi Melv,

I think the "-" characters in your range causing the problem.

Now you have the solution with SUM(IF( {array} formula, here is the SUMPRODUCT version, with just enter:

=SUMPRODUCT(('2015'!$B$2:$S$2=$C7)*('2015'!$B$1:$S$1=D$6)*(SUBSTITUTE('2015'!$B$3:$S$5,"-",0)))

Regards,

 

[Khalid NGO]

Or you could use Find & Replace in your sheet 2015

Select your range [B3:S5]
Press Ctrl+H (Find & Replace)
Find what: -
Replace with: 0

Now you can use your simple SP formula:
=SUMPRODUCT(('2015'!$B$2:$S$2=$C7)*('2015'!$B$1:$S$1=D$6)*('2015'!$B$3:$S$5))

Use a custom format of:
#,##0;-#,##0;"-"
to display zeros as -

Regards,

 

[melvin]

Khalid, thanks mate. I am an addict of the sumproduct formula..lol..Appreciate your help.

Was great to learn the Sum & If array formula from Ramesh. Appreciate boys.

Melv.