[Closed] Excel - stumped

Pivot table 🙂

so in column D you have blanks or "C" and in column E you have blank or "D" and if you have a "C" in a row, you wont also have a "D" and vice versa?

And in B you have blank or "Open" yes?

and you want to know how many rows have "Open" AND either "C" or "D" yes?

Basically yes.

Column C has occurences of "open"
Column D has occurrences of "design"
Column E has occurrences of "design"

I want to count when those conditions are met.

Thanks.

=SUMPRODUCT(($B$1:$B$9="open")*($D$1:$E$9="design"))

This counts the number of designs in the rows IF there is an Open in column B.

So if there was an Open in B10, and a design in C10 AND D10 that would count as 2. Do you want that to only count as 1?

Got to go and pick up kids from skool. Will tweak for you when I get back is someone else hasnt helped out.

If there is a possibility of both columns D and E having a value of "design", the easiest thing is to add an F column that flags when "design" occurs i.e. =IF(OR($D3="Design", $E3="Design"),1,0) (and hide the column after doing the count if it looks ugly!). Though I am sure Stoner will come up with something more eloquent.

You can use SUMIFS() or COUNTIFS() if that is more intuitive to you, which sum one range based on a criteria on another (potentially different range) as long as it has the same length.

i.e. if you don't have duplicates
=COUNTIFS(NAT!$D$3:$D$1000,NAT!$C$3:$C$1000,"Open")+COUNTIFS(NAT!$E$3:$E$1000,NAT!$C$3:$C$1000,"Open")

or if you add a new column with flag
=COUNTIFS(NAT!$F$3:$F$1000,NAT!$C$3:$C$1000,"Open")

Bodged it to cover up my error in first post, and works a charm.

=SUMPRODUCT((NAT!C1:C1000="open")*(NAT!$D$1:$E$1000="design"))

Thanks very much 😀

EDIT: Thanks Konagirl, thankfully there won't be an occurence of that scenario, but that's a useful forumla though for later on, so thanks!