Sumproduct or countifs with multiple criteria

tamesh.d

New Member
Joined
Sep 3, 2011
Messages
40
Need help-
HAVING DATA like this-

A-Name
B-Year
C-Month
name
20111
name20122
#N/A20103
#N/A20154
name20125
name20131
#N/A20142

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


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

Month201020112012201320142015
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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..
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.....
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top