Need two different data types to give me a total

[silk723]

I'm not an Excel expert, but I'm pretty sure there's a formula for what I
need to obtain a total.

I need to determine what my total (count?) would be if d2:d30="x" and
g2:g30=not blank.

Can anyone help me determine the formula to get this total?

Thanks in advance.

 

[Ashish Mathur]

Hi,

Try this

=sumproduct(($D$2:$D$30="x")*($G$2:$G$30<>""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Try one of these...

Excel 2007 only:

=COUNTIFS(D230,"x",G2:G30,"<>")

Any version of Excel:

=SUMPRODUCT(--(D230="x"),--(G2:G30<>""))

 

[silk723]

Perfect!!! Thank you very much!
--
twc

Hi,

Try this

=sumproduct(($D$2:$D$30="x")*($G$2:$G$30<>""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Perfect!!! Thank you very much!

 

[silk723]

I spoke too soon... :-(

The formula that Ashish provided worked, but there is a teeny problem - it's
giving me the total, minus one. My total should be 34, it's coming out 33.
I've checked all cells to make sure something wasn't hiding that I didn't
see, but nothing was there. Any idea as to what might cause the undercount?

Here's my formula:

="Total Number of Systems: "&SUMPRODUCT(($D$4:$D$58="ü")*($G$4:$G$58<>""))

 

[silk723]

Ignore the question...I found the problem. The wetware can't count...