COUNTIF/SUMIF comparing two rows of data

[urselhempel]

Hello,

I have rows of data where in row 1 there is a threshold value (problem
is that the threshold is different for all samples from A to for
example E). In row 2 I have a different value for each sample. I now
want to count all the samples in row 2 where the value in row 2 is
greater than the corresponding value in row 1.

I also want to do a sumif for that.

So my data is like this:

1A: 50 1B: 71 1C: 50 1D: 50 1E: 50
2A: 51 2B: 0 2C: 52 2D: 70 2E: 5

The count would give out 3 (counting 2A, 2C and 2D), the sum would
give out 173.

Any solution for my problem?

 

[porter444]

I would use SUMPRODUCT for this kind of thing.

This page explains how to use, and gives some examples:
http://www.contextures.com/xlFunctions01.html#SumProduct

 

[Chip Pearson]

To get the count of numbers in Row 2 that are greater than the corresponding
values in Row 1, use

=SUM(--(A1:E1<A2:E2))

To get the sum of the numbers in row 2 that are greater than the
corresponding value in Row 2, use

=SUM(--(A1:E1<A2:E2)*(A2:E2))

These are both array formulas, so you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula in the
formula bar enclosed in curly braces { }.

See www.cpearson.com/Excel/ArrayFormulas.aspx for more information about
array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[urselhempel]

Great! That solves it. Thank you so much for saving me