Sum with both Horizontal and vertical criteria

tshippy21

New Member
Joined
Jul 29, 2014
Messages
13
I need to have excel sum a column when the header, matches and another column references NQ , so I want the value of what was not quoted ( I am using the current price as the value)

Current PriceEntecKingfaRhetechTaroplastWPPTT Mex
$367,837.1732 $ 425,164.89 $ 472,248.80 $ 212,055.88 NQ $ 357,477.77 NQ
$25,205.5782 $ 29,249.18 NQ $ 73,892.23 NQ $ 24,588.40 NQ
$14,696.1261 $ 12,287.58 NQ $ 5,491.53 NQ $ 14,336.28 NQ
$105,814.3840 $ 199,464.98 NQ $ 53,966.09 NQ NQ NQ
$543,209.8880 $ 471,142.93 NQ $ 414,509.66 NQ $ 531,730.21 NQ
$23,729.4123 NQ $ 19,599.31 NQ NQ $ 23,249.42 NQ
$29,089.3525 NQ NQ NQ NQ NQ NQ
$17,858.9541 NQ NQ NQ NQ NQ NQ
$156,642.0750 NQ NQ $ 81,599.32 NQ NQ NQ
$4,116.5840 NQ NQ NQ NQ NQ NQ
$71,244.1453 $ 57,712.72 NQ $ 42,201.81 NQ $ 69,946.90 NQ
$0.0000 $ - $ - $ - NQ $ - NQ
$38,550.7710 NQ NQ NQ NQ NQ NQ
$2,266.7120 NQ NQ NQ NQ NQ NQ
$1,650.6000 NQ NQ NQ $ 1,593.30 NQ NQ
$205,482.7814 NQ NQ NQ $ 191,509.88 NQ NQ
$33,719.4000 NQ NQ NQ $ 32,318.00 NQ NQ
$28,661.4900 NQ $ 40,200.00 NQ $ 27,470.30 NQ NQ
$236,044.0530 NQ NQ NQ $ 227,762.52 NQ NQ
$25,476.1268 NQ NQ NQ $ 26,322.22 NQ NQ
$9,775.3838 NQ NQ NQ $ 7,877.51 NQ NQ
$3,914.2800 NQ NQ NQ $ 3,977.60 NQ NQ
$551,259.4887 NQ NQ NQ $ 543,797.58 NQ NQ
$36,704.6280 NQ $ 38,447.06 NQ $ 34,207.36 NQ NQ

<colgroup><col><col span="2"><col span="2"><col><col></colgroup><tbody>
</tbody>

I tried '=SUMIFS('AC'!$T$8:$T$120,'AC'!$T$7:$AB$7,$A9,'AC!$U$8:$AB$120,"NQ")
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

tshippy21

New Member
Joined
Jul 29, 2014
Messages
13
That works great thank you! So that I can get it right going forward can you tell my why yours works and mine doesn't?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
You are welcome.

Yours and my formula are different. Note that in my formula this part
INDEX(AC!$8:$AB$120,0,MATCH(A9,AC!$7:$AB$7,0))
returns the desired column (criteria column), that is, the one whose header is equal to the value in A9.

To see what my formula does, step by step, try Formulas> Evaluate Formula

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,521
Messages
5,529,325
Members
409,863
Latest member
stacy09
Top