Penny Arcade Forums› Help / Advice Forum
As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Excel Help

Kick_04Kick_04 Registered User regular
edited October 2014 in Help / Advice Forum
I have a spread sheet where I keep items that I purchase at work. Previously I was only ordering items for 2-3 people, now I am going to be ordering for 12-14 people (3 departments). So I am trying to clean up my spreadsheet some and also make it more functional.

In column G I have "Purchase Order" & in column H I have "Received Date"

What I am wanting to do is count the blank cells in column H. However, with purchasing items from multiple suppliers and multiple months there are spaces to separate each purchase order. So if I just do =COUNTBLANK it will return the wrong number.

I was hoping there was a way to count cells in Column G that had text and if there was text in it to count Column H and see if blank or not, if blank count it, if H has text ignore it.



Example below I want it to count 5&6 but ignore 1,2,3,&4 
89094-572, Large DuraFlock Gloves	Case	1	$210.58	$210.58 	10/15/2014	40056968	10/22/2014
14670-149, General Transfer Pipets 	Case	1	$416.46	$416.46 	10/15/2014	40056968	10/27/2014
							
 A-E                          110271							
L15008, 500g Hydroxyacetone 95%	Bottle	1	$83.70	$83.70 	10/28/2014	40057361	
A14740, 2500g Dichloroacetic Acid 99%	Bottle	1	$118.00	$118.00 	10/28/2014	40057361

PSN id - kickyoass1
PaD id - 346,240,298
Marvel FF - Lil bill12
Kick_04 on

Posts

  • Options
    DaenrisDaenris Registered User regular
    edited October 2014
    Well, it's pretty much guaranteed that someone will come along with a better solution, but one way would be to first create a new column (say I) with a formula of:

    =IF(AND(ISTEXT(G1),ISBLANK(H1)),1,0)

    Drag that down your spreadsheet, and then sum that column.

    There's a countif and countifs function, but you're very limited on what you can use as criteria expressions, so I don't know that there's a direct way to use them for this.

    Daenris on
  • Options
    MrDelishMrDelish Registered User regular
    countifs(columnG, "="&"", columnH, "<>"&"") will work

  • Options
    Kick_04Kick_04 Registered User regular
    Daenris wrote: »
    Well, it's pretty much guaranteed that someone will come along with a better solution, but one way would be to first create a new column (say I) with a formula of:

    =IF(AND(ISTEXT(G1),ISBLANK(H1)),1,0)

    Drag that down your spreadsheet, and then sum that column.

    There's a countif and countifs function, but you're very limited on what you can use as criteria expressions, so I don't know that there's a direct way to use them for this.

    I copied & pasted your formula into column I and dragged it down. All it does is show "0" for everything.
    MrDelish wrote: »
    countifs(columnG, "="&"", columnH, "<>"&"") will work

    When I copied this formula into Excel all I get is #Name? error. If I change columnG to G4:G100 and the same for columnH it gets rid of the error, but only returns 0.

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
  • Options
    MrDelishMrDelish Registered User regular
    Is column G genuinely empty or does it have spaces?

  • Options
    Kick_04Kick_04 Registered User regular
    I want it to count the yellow squares, not the Red or Light Red squares. The normal spread sheet would not have the colors, added those to see better.
    ao3xpga1vqh6.png

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
  • Options
    DaenrisDaenris Registered User regular
    Kick_04 wrote: »
    Daenris wrote: »
    Well, it's pretty much guaranteed that someone will come along with a better solution, but one way would be to first create a new column (say I) with a formula of:

    =IF(AND(ISTEXT(G1),ISBLANK(H1)),1,0)

    Drag that down your spreadsheet, and then sum that column.

    There's a countif and countifs function, but you're very limited on what you can use as criteria expressions, so I don't know that there's a direct way to use them for this.

    I copied & pasted your formula into column I and dragged it down. All it does is show "0" for everything.
    MrDelish wrote: »
    countifs(columnG, "="&"", columnH, "<>"&"") will work

    When I copied this formula into Excel all I get is #Name? error. If I change columnG to G4:G100 and the same for columnH it gets rid of the error, but only returns 0.

    Hmm. It definitely should work. When you're dragging are the references updating correctly (G2, G3, etc)?

  • Options
    Kick_04Kick_04 Registered User regular
    Yeah the 0's in column I is the equation & shows G5/H5 than G6/H6

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
  • Options
    MrDelishMrDelish Registered User regular
    swap the G and H columns in my formula. My bad, I think

  • Options
    Kick_04Kick_04 Registered User regular
    MrDelish wrote: »
    swap the G and H columns in my formula. My bad, I think

    Thank you. That did it.

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
  • Options
    DaenrisDaenris Registered User regular
    whoops. I tested with text instead of numbers so the istext function is probably failing. It could be replaced by: NOT(ISBLANK(G1))

  • Options
    Kick_04Kick_04 Registered User regular
    Daenris wrote: »
    whoops. I tested with text instead of numbers so the istext function is probably failing. It could be replaced by: NOT(ISBLANK(G1))

    That does work as well. Thanks, I am going to stick with the other one however. Have a concern I will forget to copy over columnI from year to year with it whited out. Not that big of a problem, just take a moment to figure out why it is no longer counting lol.

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
Sign In or Register to comment.
Penny Arcade Forums› Help / Advice Forum