# Sumproduct or countifs with multiple criteria

#### tamesh.d

##### New Member
Need help-
HAVING DATA like this-

 A-Name B-Year C-Month name 2011 1 name 2012 2 #N/A 2010 3 #N/A 2015 4 name 2012 5 name 2013 1 #N/A 2014 2

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

need below output as year wise month wise total excluding #N/A

 Month 2010 2011 2012 2013 2014 2015 1 2 3 4 5 6 7 8 9 10 11 12

<colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody>
</tbody>

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
What do you mean total? You don't have any quantitative data there at all?

count the data year wise month wise like countifs but its includes #N/A too,
i need to count only data excluded N/A

now clear ....or something else i need to clarify..

Excel 2010
ABCDEFG
1A-NameB-YearC-Month
2name20111
3name20122
4#N/A20103
5#N/A20154
6name20125
7name20131
8#N/A20142
9
10
11
12Month201020112012201320142015
131010100
142001000
153000000
164000000
175001000
186000000
197000000
208000000
219000000
2210000000
2311000000
2412000000

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B13=COUNTIFS(\$A\$2:\$A\$8,"<>#N/A",\$B\$2:\$B\$8,B\$12,\$C\$2:\$C\$8,\$A13)

</tbody>

<tbody>
</tbody>

ITS SHOWING #Value ERROR...

any solution

What cell are you putting the formula in and what range is your source data in?

You ought to download the add-in for HTML Maker so you can show people your sheet properly. Hard to troubleshoot with the little bit of information you've provided.

putting the formula into cell C12
=COUNTIFS(Sheet2!\$AL\$3:AL500,"<>#N/A",Sheet2!\$HO\$3:HO506,Sheet3!C\$11,Sheet2!\$HP\$3:HP506,Sheet3!\$A12)

What cell are you putting the formula in and what range is your source data in?

You ought to download the add-in for HTML Maker so you can show people your sheet properly. Hard to troubleshoot with the little bit of information you've provided.

You need to lock the 2nd cell in all of those ranges for absolute position. Is there any reason why you are extending the year and month ranges 6 rows longer than the name range?

You need to lock the 2nd cell in all of those ranges for absolute position. Is there any reason why you are extending the year and month ranges 6 rows longer than the name range?

Thanks a lot its solved....

no reason, its not locked.....

Replies
0
Views
349
Replies
3
Views
764
Replies
3
Views
321
Replies
0
Views
355
Replies
1
Views
391

1,196,264
Messages
6,014,321
Members
441,814
Latest member
youngstubbs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back