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")
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Maybe...

=SUMIFS(AC!$T$8:$T$120,INDEX(AC!$U$8:$AB$120,0,MATCH(A9,AC!$U$7:$AB$7,0)),"NQ")

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

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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