countif = < > AND value in adjacent columns match criteria

[crafty_girl]

Col D Col G Col H
-160 VALUE IPG
20 VOLUME TSG

Example:
I'm trying to count values in column D that are greater than 10 and less
than 20 IF column G = VALUE AND column H = IPG. I have been struggling with
this since yesterday and just can't get it right. Does anyone know how to
write the formula so it will work?

Thanks!

 

[aidan.heritage]

DSUM could potentially do it for you - or an array formula would do it

=SUM(IF(D1923>0,IF(D1923<20,IF(G19:G23="value",IF(H19:H23="IPG",1,0)))))


entered with ctrl shift enter

(change my 19 and 23 to the actual start and finish of the data range

 

[crafty_girl]

Thanks Aidan!

I'm trying this example and playing with it. I'm wondering what the 1,0 at
the end represents and if I need to modify it at all. Would the DSUM work if
I'm not trying to SUM but COUNT the number of values that are greater than 10
& less than 20? I have several ranges I need to summarize based on the
corresponding column information in columns G & H that look like this:

For VALUE IPG:
COUNT numbers matching the below ranges.

No Data
Early >10 days
Early 5-10 days
Early 3-4 days
Early 1-2 days
On-Time
Late 1-2 days
Late 3-4 days
Late 5-10 days
Late > 10 days

Then do the same thing for VALUE TSG, etc. Did I present this originally in
a way that represents the goal?

THANKS AGAIN!

 

[Heather Heritage]

(home now, so have my wifes email details!)

The 1,0 work with the array formula and return a 1 if true and a zero if
false - thus SUM gives the right result because it add's all the ones up -
it's a difficult thing to get to grips with in an array formula. I like the
concept of the D functions, BUT I've never yet got any of them to work!!!