Combining Sum and Counif

[Michelle Tucker]

I need to return the value of all of those with "DP8" in A:A which also have
an entry against them in G:G
Combination of Sum and If or Countif?

 

[duane]

can you explain a little more? what do you mean by "return the value"
the sum? all of the individaul data? the count? what sort of entry i
column G

 

[Michelle Tucker]

Sorry!
I have Column A with lots of product codes in, and column G with invoice
numbers in. I want to do a sum which shows me how many of a particular
product is still unsold.
So there may be 4 entries for a "DP8", and 3 of them have an invoice number
in column G, which indicates they are sold. I would like it to do a count to
show me that there is 1 left unsold.
Thanks

 

[JulieD]

Hi Michelle

so in other words, you want a count of DP8 where there is no entry in G:G
one way
=SUMPRODUCT(--(A1:A100="DP8F"),--(G1:G100=""))

where your range is from rows 1 to 100 - edit to suit

Cheers
JulieD

 

[duane]

=countif(a1:a1000,"DP8")-sumproduct((a1:a1000="DP8")*(G1:g1000<>""))

counts the occurance of DP8 in col A and subtracts the number of time
the DP8s have an entry in col