SUMPRODUCT with COUNTIF of unique values

[Brian G]

I have a workbook to detail status of electrical motors which are located at various site locations with unique location numbers(ABC1234567,.......) in Column A, location area code (North, South, East, West, Central) in Column B, location status (Operational, Abandoned) in Column C, skid status (Online, Offline, Maintenance) in Column D, motor status within skid (Online, Offline, Maintenance, Removed) in Column D.


I have a Summary sheet which includes single value numbers for given site location, etc. As there are multiple motors on multiple skids at each location I obviously have to deal with duplicates, specifically with Column A site locations.


I am using Excel 2003, and the SUMPRODUCT function. This works well is providing me some of the values for the summary, but I am really struggling getting a workable formula for providing me with a number of site locations given a set series of given motor status. I am using the formula below but I am getting a decimalised value and not a whole number. For example I know that there are 98 sites that match a given status but formula gives an incorrect decimalised value.


=SUMPRODUCT((Data!$B$3:$B$500="North")*(Data!$C$3:$C$500="Operational")*(Data!$D$3:$D$500="Maintenance")*(Data!$E$3:$E$500="Online")/(COUNTIF(Data!$A$13:$A$500,Data!$A$3:$A$500)))


I have looked at the formula evaluation tool and can see all appears well in the SUMPRODUCT section (FALSE and TRUE giving 1 and 0), but when in the COUNTIF section where there are duplicates the 1 for true becomes 0.5, etc when a duplicate site exists.

I have tried multiple solutions form lots a Google searches, so a post for help is now required. Worth saying I can get a answer using PIVOT tables and advance filters, but I want a working formula for flexibility.

 

[Debraj]

Hi Brian G,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


* COUNTIF(Data!$A$13:$A$500,Data!$A$3:$A$500)

I hope above should be A3 not A13.. as Sumprouct cant handle non-parallel Range.


* I wish you have Excel prior to 2007.. You can use the below Formula..

Using Ctrl+Shift+Enter

{=SUM((IF((Data!$B$3:$B$500="North")*(Data!$C$3:$C$500="Operational")*(Data!$D$3:$D$500="Maintenance")*(Data!$E$3:$E$500="Online"),1/COUNTIFS(Data!$B$3:$B$500,"North",Data!$C$3:$C$500,"Operational",Data!$D$3:$D$500,"Maintenance",Data!$E$3:$E$500,"Online",Data!$A$3:$A$500,Data!$A$3:$A$500),"")))}


or


{=SUMPRODUCT((Data!$B$3:$B$500="North")*(Data!$C$3:$C$500="Operational")*(Data!$D$3:$D$500="Maintenance")*(Data!$E$3:$E$500="Online"),COUNTIFS(Data!$B$3:$B$500,"North",Data!$C$3:$C$500,"Operational",Data!$D$3:$D$500,"Maintenance",Data!$E$3:$E$500,"Online",Data!$A$3:$A$500,Data!$A$3:$A$500))}

Please let us know if any changes need to be done..

Regards,

Deb

 

[SirJB7]

@SirJB7

Hi, myself!

So long...

Where have I read this? It sounds so familiar to me...

Regards!

PS: I should've copyrighted it, nowadays I'd be a millionaire

 

[Debraj]

Thank God..

Chandoo's website doesn't have embedded code for Handwriting Recognition Software..


Regards,

Deb


PS: If I really have to pay.. Revenue, for each of the thing I have learned from this Forum..

... oops... Don't ask how much I need to pay to NINJA's..


PPS: If you look closely.. bottom part is also adapted from some of you..and feel proud to say it loudly.. but its not claimable..

 

[SirJB7]

@Debraj Roy

Hi!

Thanks for the link about handwriting recognition, very interesting. But I'd like to see Srihari trying to deal with many of my handwritten texts. My calligraphy has been always so perfect that only with the advent of earlier word processors I could recognize my own writings, and as my environment say only if they're in Times New Roman, never by hand.

That's the strength of this community: the will for sharing. And sometimes -and not eventually- Ninjas are the new knowledge providers, we too learn form Members.

Regards!

PS: Only the first 6 paragraphs sound familiar to me (if we accept a , by a ! in 1st one), just 'til the suspension points.

PS 2: Hereby I grant you the left to use, misuse and abuse of the above mentioned texts and other related stuff on a royalty free basis accordingly to this Act.